group by having a unique id

Question

I'm trying to do a group by. However, this unique id field prevents me to do it.

Can you please help me to resolve this issue.

thanks.

scenario 1

select box.name, box.description, box.id
from box
group by box.name, box.description, box.id
order by box.id

update: I've already solved the first scenario http://sqlfiddle.com/#!6/111e7/16/0

scenario 2

select box.name, box.description, box.id, sum(box.amount) as amount
from box
group by box.name, box.description, box.id
order by box.id

update: I've already solved the second scenario http://sqlfiddle.com/#!6/449b7/2

Please provide the table structure - otherwise the request doesn't make sense at all: grouping only by a unique ID is NOT possible. — Tyron78, Oct 26 '17 at 08:06
Please add the query you're trying to make and the response you get. This question is impossible to answer without that — ShamPooSham, Oct 26 '17 at 08:07
What you need is some kind of aggregate function such as COUNT(_columnname_), otherwise it doesn't know what to do with the grouping — ShamPooSham, Oct 26 '17 at 08:16
Btw, what are your goals with using group by? The way you use it doesn't make a lot of sense. You should group by a subset of the columns, not all of them — ShamPooSham, Oct 26 '17 at 08:18
Yeah, it is really unclear what you are hoping to achieve here. You need to explain why you need to group, and why you need to order. — Keith, Oct 26 '17 at 08:23
Possible duplicate of [How can I remove duplicate rows?](https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Valerica, Oct 26 '17 at 08:40
If you only need to order the query results, remove the group by line: select box.name, box.description, box.id from box order by box.id — KarelHusa, Oct 26 '17 at 09:02

Stanislav Kundii · Answer 1 · 2017-10-26T08:40:22.957

1

For remove duplicate

;WITH xx AS
(
    select 
        [rn] = ROW_NUMBER() OVER (PARTITION BY ID, ORDER BY Id),
        name, description, id
    from box    
) 
SELECT 
*
FROM xx
WHERE 
    [rn] = 1

Or

SELECT TOP 1 WITH TIES
*
FROM xx
ORDER BY 
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Id)

edited Oct 26 '17 at 08:40

answered Oct 26 '17 at 08:35

Stanislav Kundii

2,874
1
11
17

score 0 · Answer 2 · answered Oct 27 '17 at 00:01

One approach would be to keep either the MAX or MIN box id and use that to order by. This will remove duplicates and still leave you a value to order by. For example (using MAX)

select box.name, box.description, MAX(box.id) box_id, sum(box.amount) as amount
from box
group by box.name, box.description, box.id
order by MAX(box.id)

group by having a unique id

2 Answers2