Query

[Tester09]

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]

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

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

 

[Tester09]

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]

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]

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]

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]

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]

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

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]

Thanks PHV and r937.

 

[Tester09]

r937, thanks. Your code works.