March 23, 2009 at 3:48 am
Hi All,
I have a table with an index made of two fields ( A, B)
I need to select all records with unique A and greatest B among records with the same A
Example :
Table records
A B C
1 1 a
1 2 a
3 2 b
3 3 c
4 1 d
4 2 e
4 2 d
records I need in my query results
A B C
1 2 a
3 3 c
4 2 d
How do I do that ?
March 23, 2009 at 11:29 am
If field B is an integer then you can use ...
SELECT A, MAX(B) AS B
FROM Table
GROUP BY A
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 23, 2009 at 1:00 pm
Hi Mohit,
Thanks for your reply
The Table has more fields which I need in my query .
They should be taken from the right record as in that example
Table records
A B C
1 1 x
1 2 y
3 3 a
4 1 b
4 2 c
records I need in my query results
A B C
1 2 y
3 3 a
4 2 c
March 23, 2009 at 1:08 pm
selecttop 1
A, B, ... other columns ...
fromMyTable
order byA, B desc
March 23, 2009 at 1:37 pm
I see ..
It is usually easier to provide an answer if you provide a CREATE TABLE and sample data .. If you can do that I might be able to help you out.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 23, 2009 at 1:57 pm
Read the article I link to in my signature to find out how to post your question so it can be answered better/faster. What you are looking for is something like:
WITH maxRows (A, B)
AS (SELECT A, MAX(B) FROM table GROUP BY A)
SELECT t.A, t.B, {other columns}
FROM table t
JOIN maxRows m ON m.A = t.A AND m.B = t.B;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2009 at 4:09 am
Thanks Guys,
Jeffrey's answer did the job.
Next time I'll do my homework regarding how to post to this forum .
Regards
Nir
April 11, 2009 at 8:45 pm
Your first example confuses me a little. (Why 42d and not 42e? Typo?)
However, if you are only wanting to get one single row back for each unique A, then this could also work for you, and possibly faster than the GROUP BY / JOIN.
declare @temp table (A int, B int, C char(1))
insert into @temp
select 1, 1, 'a' union all
select 1, 2, 'a' union all
select 3, 2, 'b' union all
select 3, 3, 'c' union all
select 4, 1, 'd' union all
select 4, 2, 'e' union all
select 4, 2, 'd'
select * from @temp
;with cte as (select *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) as rowID from @temp)
select A,B,C from cte where rowID = 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 17, 2009 at 3:55 am
Bob Hovious (4/11/2009)
Your first example confuses me a little. (Why 42d and not 42e? Typo?)However, if you are only wanting to get one single row back for each unique A, then this could also work for you, and possibly faster than the GROUP BY / JOIN.
declare @temp table (A int, B int, C char(1))
insert into @temp
select 1, 1, 'a' union all
select 1, 2, 'a' union all
select 3, 2, 'b' union all
select 3, 3, 'c' union all
select 4, 1, 'd' union all
select 4, 2, 'e' union all
select 4, 2, 'd'
select * from @temp
;with cte as (select *,ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) as rowID from @temp)
select A,B,C from cte where rowID = 1
Thanks Bob ,
(It doesn't matter if it's 4/2/d or 4/2/e since it should be unique A (4) and greatest B (2), C field has no effect)
Your way works too .
How can we know which way is more efficient ?
Do you know about a tool which can compare efficiency of two queries ?
Nir
April 17, 2009 at 6:53 am
I'm really glad to hear that are you concerned about efficiency. Here are the tried and true ways to measure performance.
declare @timer datetime
Set statistics time on; -- measure cpu load
Set statistics io on; -- measure reads and writes
set @timer = getdate()
-- run some code
Set statistics time off;
Set statistics io off;
select datediff(ms,@timer,getdate()) as elapsedTime -- just see how fast it runs
For small tests, I usually don't do all three measurements at the same time, to keep them from affecting each other. For larger runs, I usually don't care about a couple of milliseconds difference.
To get measurable differences, you may have to increase the size of your input table. I can give you some code to generate big test tables of varying sizes with random values if you like. But be warned, some techniques work better for small volumes than with large volumes. The answer to which is best is often "It depends." Testing can be addictive but very, very educational. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply