How to select the first row from group by date

Question

I am writing a program for amateur radio. Some callsigns will appear more than once in the data but the qsodate will be different. I only want the first occurrence of a call sign after a given date.

The query

select distinct 
  a.callsign, 
  a.SKCC_Number,
  a.qsodate,
  b.name,
  a.SPC,
  a.Band
from qso a, skccdata b
where SKCC_Number like '%[CTS]%'
  AND QSODate > = '2014-08-01'
  and b.callsign = a.callsign
order by a.QSODate

The problem:

Because contacts occur on different dates, I get all of the contacts - I have tried adding min(a.qsodate) to get only the first but then I run into all sorts of issues regarding grouping.

This query will be in a stored procedure, so creating temp tables or cursors will not be a problem.

score 2 · Answer 1 · answered Oct 26 '14 at 14:18

You can use the ROW_NUMBER() to get the first row with the first date, like this:

WITH CTE
AS
(
    select 
      a.callsign, 
      a.SKCC_Number,
      a.qsodate,
      b.name,
      a.SPC,
      a.Band,
      ROW_NUMBER() OVER(PARTITION BY a.callsign ORDER BY a.QSODate) AS RN
    from qso a,skccdata b
    where SKCC_Number like '%[CTS]%'
      AND QSODate > = '2014-08-01'
      and b.callsign = a.callsign
) 
SELECT * 
FROM CTE 
WHERE RN = 1;

ROW_NUMBER() OVER(PARTITION BY a.callsign ORDER BY a.QSODate) will give you a ranking number for each group of callsign ordered by QSODate, then the WHERE RN = 1 will eliminate all the rows except the first one which has the minimum QSODate.

Grantly · Answer 2 · 2014-10-26T14:40:16.410

Have you tried starting your query with SELECT TOP 1 ...(fields) Then you will only get one row. You can use TOP x .... for x number of rows, or TOP 50 PERCENT for the top half of the rows, etc. Then you can eliminate DISTINCT in this case

EDIT: misunderstood question. How about this?

select 
  a.callsign, 
  a.SKCC_Number,
  a.qsodate,
  (SELECT TOP 1 b.name FROM skccdata b WHERE b.callsign = a.callsign) as NAME,
  a.SPC,
  a.Band
from qso a
where SKCC_Number like '%[CTS]%'
  AND QSODate > = '2014-08-01'
GROUP BY a.QSODate, a.callsign, a.SKCC_Number, a.SPC, a.Band
order by a.QSODate

and add callsign to your where clause to isolate callsigns

This won't work as the OP wants the TOP 1 row for each group, not the TOP 1 for the end result set. — jpw, Oct 26 '14 at 14:23

How to select the first row from group by date

2 Answers2