selecting first row from GROUP BY

[karthik]

Hi,
I having problems returning only the first row of data for each GROUP
for the query below. Can someone please help? Thanks.

SELECT bid, xact_date, xact_time
FROM badge_history bh
GROUP BY bid, xact_date, xact_time
ORDER BY bid, xact_date DESC, xact_time DESC

So if the data returned by the above query is:

bid xact_date xact_time
-------------------------------------------------
0000000025697510 20021205 111822
0000000025697510 20021205 111816
0000000025697510 20021205 111810
151744034643 20030122 113244
151744034643 20030117 165815
151744034643 20030117 165757
151744034643 20030117 165739
151744034643 20030117 165727

Then I need the new query to produce:

bid xact_date xact_time
-------------------------------------------------
0000000025697510 20021205 111822
151744034643 20030122 113244

Can anyone suggest anything that might work!? Thanks again.

[Simon Hayes]

<ka*****@enterpriseair.com> wrote in message
news:f8**************************@posting.google.c om...

Hi,
I having problems returning only the first row of data for each GROUP
for the query below. Can someone please help? Thanks.

SELECT bid, xact_date, xact_time
FROM badge_history bh
GROUP BY bid, xact_date, xact_time
ORDER BY bid, xact_date DESC, xact_time DESC

So if the data returned by the above query is:

bid xact_date xact_time
-------------------------------------------------
0000000025697510 20021205 111822
0000000025697510 20021205 111816
0000000025697510 20021205 111810
151744034643 20030122 113244
151744034643 20030117 165815
151744034643 20030117 165757
151744034643 20030117 165739
151744034643 20030117 165727

Then I need the new query to produce:

bid xact_date xact_time
-------------------------------------------------
0000000025697510 20021205 111822
151744034643 20030122 113244

Can anyone suggest anything that might work!? Thanks again.

This is one possibility - use a derived table to find the most recent
xact_date for each bid (or you could write it as a view as well):

select bh.bid, bh.xact_date, max(bh.xact_time)
from badge_history bh
join
(
select bid, max(xact_date) as 'maxdate'
from badge_history
group by bid
) dt
on bh.bid = dt.bid and
bh.xact_date = dt.maxdate

Simon

[David Portas]

You'd find this a lot easier if you used a single DATETIME column instead of
separate dates and times. Why use two numeric/char columns when SQL provides
a single datatype that does the job?

CREATE TABLE badge_history (bid VARCHAR(16) NOT NULL, xact_dt DATETIME
PRIMARY KEY)

INSERT INTO badge_history VALUES ('0000000025697510','2002-12-05T11:18:22')
INSERT INTO badge_history VALUES ('0000000025697510','2002-12-05T11:18:16')
INSERT INTO badge_history VALUES ('0000000025697510','2002-12-05T11:18:10')
INSERT INTO badge_history VALUES ('151744034643','2003-01-22T11:32:44')
INSERT INTO badge_history VALUES ('151744034643','2003-01-17T16:58:15')
INSERT INTO badge_history VALUES ('151744034643','2003-01-17T16:57:57')
INSERT INTO badge_history VALUES ('151744034643','2003-01-17T16:57:39')
INSERT INTO badge_history VALUES ('151744034643','2003-01-17T16:57:27')

SELECT bid, MAX(xact_dt) AS xact_dt
FROM badge_history
GROUP BY bid

--
David Portas
SQL Server MVP
--