tricky select question

  • I've been trying to get this query to work all afternoon.

    I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.

    there are 2 things that I need

    1. make the records unique like select distinct A, B

    2. I need to bring the datetime C with the record that has the most current date.

    If I run

    select distinct A, B from table

    this gives me the right records, field c is eliminated

    If I run

    select distinct A, B, C

    rows show that have different dates in field C. I only need one record with the most current date records

    anyone have any thoughts

  • TaffyLewis (12/14/2007)


    I've been trying to get this query to work all afternoon.

    I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.

    there are 2 things that I need

    1. make the records unique like select distinct A, B

    2. I need to bring the datetime C with the record that has the most current date.

    try

    select A, B, Max(C) as C

    from tablename

    group by A, B

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • select A, B, MAX(C) AS MaxC

    from Yourtbl

    group by A,B

  • I think I made a mistake in trying to explain this. I tried to make this simple for an explanation, but I think I need to add that these come from two tables. When I tried the solution, I got errors. These fields come from different tables. Let's try this.

    select t1.A, t1.C, t2.C

    from t1 inner join t2

    on t1.A = t2.A

    and t1.C = t2.C

    t2.c is the date field

    When I try to run the Max on this I generate the following error:

    Column 't2.C' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    sry about trying to make this too simple, but I really could use the help.

  • Here is what would really help, provide us with the DDL for the tables, some sample data for the tables, and your expected results based on the sample data.

    Jeff Moden worte an excellent article on how to ask for help that will give you a good idea of what we could use to help us help you.

    😎

  • TaffyLewis (12/14/2007)


    select t1.A, t1.C, t2.C

    from t1 inner join t2

    on t1.A = t2.A

    and t1.C = t2.C

    t2.c is the date field

    t1.C and t2.C are the same values. Your join assures that. So, this should be what you need. Why return 2 columns that are the same?

    select t1.A, MAX(t2.C)

    from t1 inner join t2

    on t1.A = t2.A

    and t1.C = t2.C

    GROUP BY t1.A

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • TaffyLewis (12/14/2007)


    select t1.A, t1.C, t2.C

    from t1 inner join t2

    on t1.A = t2.A

    and t1.C = t2.C

    t2.c is the date field

    When I try to run the Max on this I generate the following error:

    Column 't2.C' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    IN that case try :

    select t1.A, t1.C, MAX(t2.C) as T2C

    from t1 inner join t2

    on t1.A = t2.A

    and t1.C = t2.C

    group by t1.A, T1.C

    from what I see your problem is exactly what the interpreter says, you just need that group by clause in there.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • ok, I'll take a look at that and create the quesiton better.

    For anyone who might read this and have not read the article, here it is:

    Forum Etiquette: How to post data/code on a forum to get the best help

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Here it is better asked!

    If OBJECT_ID('TestA','U') is not null

    drop table TestA

    CREATE TABLE [TestA]

    (

    [comp_id] [char](5) NOT NULL,

    [applcnt_id] [varchar](16) NULL

    )

    If OBJECT_ID('TestB','U') is not null

    drop table TestB

    CREATE TABLE [TestB]

    (

    [comp_Id] [char](5) NOT NULL,

    [applcnt_id] [varchar](16) NOT NULL,

    [needed_dt] [datetime] NULL

    )

    INSERT INTO TestA

    (comp_id, applcnt_id)

    SELECT 'MMMM','MMMM000000000015' UNION ALL

    select 'MMMM','MMMM000000000025'

    INSERT INTO TestB

    (comp_id, applcnt_id,needed_dt)

    SELECT 'MMMM','MMMM000000000015','2007-11-15 00:00:00.000' UNION ALL

    select 'MMMM','MMMM000000000025','2007-11-15 00:00:00.000' UNION ALL

    select 'MMMM','MMMM000000000025','2007-11-14 00:00:00.000'

    --basic query that gives 3 rows

    select TestA.comp_id, TestA.applcnt_id, TestB.needed_dt

    From TestA inner join TestB

    on TestA.comp_id = TestB.comp_id

    and TestA.applcnt_id = TestB.applcnt_id

    --if I use distinct I get two rows

    select distinct TestA.comp_id, TestA.applcnt_id

    From TestA inner join TestB

    on TestA.comp_id = TestB.comp_id

    and TestA.applcnt_id = TestB.applcnt_id

    --I would like to get the two rows, but with one datetime field

    -- and with the current datetime

    --when I try max and group by I get errors

    select TestA.comp_id, TestA.applcnt_id, Max(TestB.needed_dt) As NeededMax

    From TestA inner join TestB

    on TestA.comp_id = TestB.comp_id

    and TestA.applcnt_id = TestB.applcnt_id

    TaffyLewis (12/14/2007)


    I've been trying to get this query to work all afternoon.

    I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.

    there are 2 things that I need

    1. make the records unique like select distinct A, B

    2. I need to bring the datetime C with the record that has the most current date.

    If I run

    select distinct A, B from table

    this gives me the right records, field c is eliminated

    If I run

    select distinct A, B, C

    rows show that have different dates in field C. I only need one record with the most current date records

    anyone have any thoughts

  • I figured it out. Going through the steps and testing to make sure I got it right showed me that I used the group by incorrectly.

    Thanks, Lynn, for making me read the article and go through the steps. I definately learned from it.

    Thanks again!

    🙂

    TaffyLewis (12/17/2007)


    Here it is better asked!

    If OBJECT_ID('TestA','U') is not null

    drop table TestA

    CREATE TABLE [TestA]

    (

    [comp_id] [char](5) NOT NULL,

    [applcnt_id] [varchar](16) NULL

    )

    If OBJECT_ID('TestB','U') is not null

    drop table TestB

    CREATE TABLE [TestB]

    (

    [comp_Id] [char](5) NOT NULL,

    [applcnt_id] [varchar](16) NOT NULL,

    [needed_dt] [datetime] NULL

    )

    INSERT INTO TestA

    (comp_id, applcnt_id)

    SELECT 'MMMM','MMMM000000000015' UNION ALL

    select 'MMMM','MMMM000000000025'

    INSERT INTO TestB

    (comp_id, applcnt_id,needed_dt)

    SELECT 'MMMM','MMMM000000000015','2007-11-15 00:00:00.000' UNION ALL

    select 'MMMM','MMMM000000000025','2007-11-15 00:00:00.000' UNION ALL

    select 'MMMM','MMMM000000000025','2007-11-14 00:00:00.000'

    --basic query that gives 3 rows

    select TestA.comp_id, TestA.applcnt_id, TestB.needed_dt

    From TestA inner join TestB

    on TestA.comp_id = TestB.comp_id

    and TestA.applcnt_id = TestB.applcnt_id

    --if I use distinct I get two rows

    select distinct TestA.comp_id, TestA.applcnt_id

    From TestA inner join TestB

    on TestA.comp_id = TestB.comp_id

    and TestA.applcnt_id = TestB.applcnt_id

    --I would like to get the two rows, but with one datetime field

    -- and with the current datetime

    --when I try max and group by I get errors

    select TestA.comp_id, TestA.applcnt_id, Max(TestB.needed_dt) As NeededMax

    From TestA inner join TestB

    on TestA.comp_id = TestB.comp_id

    and TestA.applcnt_id = TestB.applcnt_id

    TaffyLewis (12/14/2007)


    I've been trying to get this query to work all afternoon.

    I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.

    there are 2 things that I need

    1. make the records unique like select distinct A, B

    2. I need to bring the datetime C with the record that has the most current date.

    If I run

    select distinct A, B from table

    this gives me the right records, field c is eliminated

    If I run

    select distinct A, B, C

    rows show that have different dates in field C. I only need one record with the most current date records

    anyone have any thoughts

  • I'm glad to have been of assistance, even though all I did was point you to Jeff's article. Be sure to post a thank you to Jeff as well for writing that article.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply