locked
select case situation RRS feed

  • Question

  • User-2124142478 posted

    Hi every one; I have this sql

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    WHERE
    p.RIH IN (41)

    tihs works fine however I want to get output of this query 'show me 100. person's KKS if there are 100 persons or more, if not show me the lowest', but I dont know how to do that , I am not very good at sql.

    Any help appreciated.

    THNX

    Monday, September 12, 2011 7:34 AM

Answers

  • User-20777992 posted

    Try this!!!

    declare @cnt int

    set @cnt =

    (

    SELECT
    count(*)
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    )

    if(cnt >= 100)

    begin

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    WHERE
    p.RIH = 100

    end

    else

    begin

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p

    group by p.RIH, p.KIH,p.Kihi

    having p.RIH = max(p.RIH)

    end

    This can be made more simplified!!! But as I am running out of time. Keep this for now. Let me know if it helped!!! 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2011 3:48 AM
  • User-2124142478 posted

    I have solved my problem like below, if anyone interested...

    declare @cnt integer= (select count(*) FROM
                            (
                                SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International')

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    WHERE
    p.RIH IN (100,@cnt)

    thnx for all the answers...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2011 7:56 AM

All replies

  • User-20777992 posted

    can do something like dis!!!

    declare @Cnt int

    set @Cnt = (select count(*) from MyTable)
    print @Cnt
    if(@Cnt >= 100)
    begin
    select top 100 * from MyTable
    end
    else
    begin
    select * from MyTable
    end


    Monday, September 12, 2011 9:41 AM
  • User-1661238708 posted

    hi,

    add Limit 100 or Top 100 to your select.

    Tuesday, September 13, 2011 6:03 AM
  • User3866881 posted

    I want to get output of this query 'show me 100. person's KKS if there are 100 persons or more, if not show me the lowest',

    Hello,

    You can try this way instead:

    SELECT TOP 100
    *
    FROM
    (
    ………………

    )

    Tuesday, September 13, 2011 9:52 PM
  • User-2124142478 posted

    Thnx for your answers , however it is not working. in the code

    ........

    WHERE
    p.RIH IN (41) this is what i have to write, because i know that there are 41 data, but i will not know in the future there will be 41 people or more or less. In the future i will have to look at the data, figure out how many people there are and write- for example RIH IN(55)-. I do not want that I do not want top 100 people's data. I want 100. person's data if there are 100 people if there are less than 100 people show the last's data- for example if there are 41 people show me 41. person's data.

    Thnx

    Wednesday, September 14, 2011 2:57 AM
  • User3866881 posted

    I want 100. person's data if there are 100 people if there are less than 100 people show the last's data- for example if there are 41 people show me 41. person's data.

    Yes, what reven_gr and I are right:)

    Top 100 means that——

    If you have more than 100 people, list 100.

    If you don't have so many people, list as many people as possible.

    Wednesday, September 14, 2011 3:14 AM
  • User-20777992 posted

    There seems some problem in understanding the problem!!!

    If there are 100 or more than 100 records then you want 100th record or you want all 100 records?

    If there are less than 100 records then you want all the available records or you want the last record?

    Pl. clear it :)!!!

    Wednesday, September 14, 2011 3:20 AM
  • User-2124142478 posted

    Yes you are right :) but I want  just one output, I want to see just max row number's data, when i write top 100 it is giving me 41 output, i want 1 output. İ think i have to add something like  page.RIH IN (MAX(RIH))[i tired and not working by the way]. In the future I do not want to chage the index of page.RIH IN (41), it should do it itself.

    Thnx in advance for your answers..

    Wednesday, September 14, 2011 3:24 AM
  • User-2124142478 posted

    hi asurani;

    i want maximum rowth record not all records. if there are more than 100 records i want 100th record or if there are less than 100 records show me last record just last.

    Thnx in advance

    Wednesday, September 14, 2011 3:26 AM
  • User3866881 posted

    if there are more than 100 records i want 100th record or if there are less than 100 records show me last record just last.

    Hello, I took what you mean:)

    Now you can try this:

    using (SqlCommand cmd = new SqlCommand("select count(*) from tableName",new SqlConnection("Your conn str")))
    {
            cmd.Connection.Open();
            int num = (int)cmd.ExecuteScalar();
            if(num>=100)
            {
                  cmd.CommandText = "select top 1 * from tableName where id=100";
            }
            else
            {
                  cmd.CommandText = "select top 1 * from tableName where id=max(id)";
            }
             

           SqlDataReader sr = cmd.ExecuteReader();
           sr = cmd.ExecuteReader();
           sr.Read();

            //Take value sr["ColumnName"].ToString(); one by one
    }

    I only offered you a sample:

    You should have an Id which comes from 1 to 100 or more sequencial —— This is key.

    Wednesday, September 14, 2011 3:34 AM
  • User-20777992 posted

    Try this!!!

    declare @cnt int

    set @cnt =

    (

    SELECT
    count(*)
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    )

    if(cnt >= 100)

    begin

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    WHERE
    p.RIH = 100

    end

    else

    begin

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p

    group by p.RIH, p.KIH,p.Kihi

    having p.RIH = max(p.RIH)

    end

    This can be made more simplified!!! But as I am running out of time. Keep this for now. Let me know if it helped!!! 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2011 3:48 AM
  • User-1661238708 posted

    Try this,

    Select max(id) From (Select Top 100 from [yourTable])

    the inner select gets  the first 100 records if exist and the outer select from those the last.

    if 200 records you get the first 100 and then the 100th

    if 40 records you get all 40 and then the 40th.

    i believe this is you want!!!

     

    Wednesday, September 14, 2011 4:41 AM
  • User-2124142478 posted

    I have solved my problem like below, if anyone interested...

    declare @cnt integer= (select count(*) FROM
                            (
                                SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International')

    SELECT
    *
    FROM
    (
    SELECT
    ROW_NUMBER() OVER (ORDER BY KIH DESC) AS RIH,
    q.KIH,q.Kihi
    FROM
    (
    SELECT
    convert(float,replace((SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KKS"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC), ',', '.')) AS KIH,


    (SELECT TOP (1) XmlContent.query('/app32/fields/field[@name="KET"]').value('.[1]', 'nvarchar(max)')
    FROM Ap2AH ah WHERE ah.ApplicationId = a.ApplicationId ORDER BY CreateDate DESC) as Kihi

    FROM Ap2A a
    WHERE ApplicationTypeId = 56 AND CurrentStateId = 1006


    ) q where Kihi='International'
    ) p
    WHERE
    p.RIH IN (100,@cnt)

    thnx for all the answers...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 14, 2011 7:56 AM