Query

Tester09 · Apr 14, 2009

Hi,

I have the following result from a select statement:

Column: A B C
Data : 55 S 123
Data : 55 T 123
Data : 09 S 123
Data : 09 T 123

I am new to SQL and I cannot figure out a way to do the following: "If there are rows with same values in Column A, select the rows with 'T' in column B."

Your help is greatly appreciated. Thanks.

PHV · Apr 14, 2009

Something like this ?
SELECT A,B,C FROM yourTable WHERE B='T'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Tester09 · Apr 14, 2009

Thanks for your reply. My example is not a table, it is a result of the select statement. Probably I should create a view and try again.

Column: A B C
Data : 55 S 123
Data : 55 T 123
Data : 55 Z 123
Data : 09 S 123
Data : 09 T 123
Data : 09 S 123

Just for clarification: I want to do is "if there are rows with same Column A value, select the last row."

PHV · Apr 14, 2009

What is your meaning of last row ?
What is your actual SQL code returning the above result ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Tester09 · Apr 14, 2009

Sorry for the confusion.

Column: A B C
Data : 55 S 123
Data : 55 T 123
Data : 55 Z 123
Data : 09 S 123
Data : 09 T 123
Data : 09 S 123

I sorted the result so what I want is "55 Z 123" and "09 S 123" out of the above table. I have created a view so the original statement does not matter I guess. Thanks a lot for your help.

PHV · Apr 14, 2009

Sorry, but how is your result sorted ?
Why isn't "09 T 123" the last result ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Tester09 · Apr 14, 2009

55 Z 123" is the last row with 55 in Column A, then "09 S 123" is the last row with 09 in Column A. They are sorted by Column A and then Process_Time(let's say it's in Column D). Thanks.

r937 · Apr 14, 2009

ah, the old familiar "hidden column D i forgot to mention" trick ;-)

Code:

SELECT q.A
     , q.B
     , q.C
  FROM ( SELECT A
              , MAX(D) AS latest
           FROM queryresult
         GROUP
             BY A ) AS m
INNER
  JOIN queryresult AS q
    ON q.A = m.A
   AND q.D = m.latest

please let me know if that works for you

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

Tester09 · Apr 14, 2009

Thanks PHV and r937.

Tester09 · Apr 14, 2009

r937, thanks. Your code works.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query

Tester09

Technical User

PHV

MIS

Tester09

Technical User

PHV

MIS

Tester09

Technical User

PHV

MIS

Tester09

Technical User

r937

Technical User

Tester09

Technical User

Tester09

Technical User

Similar threads

Part and Inventory Search

Sponsor