Group By , one row per record

Question

I have data that looks similar to this:

ID | Order# | Transfer# 
1  | Abc    |   123a  
2  | Abc    |   456b 
3  | Abc    |   123a  
4  | Abc    |   NULL 
5  | Def    |   123c 
6  | Def    |   456d 
7  | Def    |   123c 
8  | Def    |   NULL

I want to run a query that returns 1 row, and it doesn't matter which transfer# is chooses (except the null).

If I run this query:

SELECT order#,
  transfer#
FROM coitem
WHERE order#   ='abc'
AND transfer# IS NOT NULL
GROUP BY order# ,
  transfer#

It returns:

Order# | Transfer# 
Abc    |   123a
Abc    |   456b 
Def    |   123c 
Def    |   456d

Is there a way to only return 1 row, either like this:

Order# | Transfer# 
Abc    |   456b  
Def    |   123c

My goal is to view top 1 transfer# for all orders in my system, with one query.

Thanks

[Standard](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) example of a [tag:greatest-n-per-group] query (where the group size is one) — Clockwork-Muse, Jan 09 '17 at 18:50

score 0 · Answer 1 · answered Jan 09 '17 at 18:44

0

You can use ROW_NUMBER() function for this purpose like

select * from (
select [Order], Transfer,
ROW_NUMBER() OVER(PARTITION BY Transfer ORDER BY Transfer) AS rn
from tbl1 ) xxx
where rn = 1 and Transfer is not null;

answered Jan 09 '17 at 18:44

Rahul

76,197
13
71
125

score 0 · Answer 2 · answered Jan 09 '17 at 18:51

0

There are a few ways to express this but for me, the most readable way would be:

SELECT
   order#,
   MAX(transfer#) AS Transfer#
FROM coitem
WHERE transfer# IS NOT NULL
GROUP BY order#

I think this matches the idea you are describing.

answered Jan 09 '17 at 18:51

Jamie F

23,189
5
61
77

Thanks Jamie, I tried that, unfortunately some orders have letters in it, the max wont work properly – user135094 Jan 09 '17 at 18:54
Max usually works on strings (`varchar`) as well as numbers. Microsoft SQL server? – Jamie F Jan 09 '17 at 19:01

Group By , one row per record

2 Answers2