question

dmonk64 avatar image
dmonk64 asked

How to make a correct grouping from this query?

I need to a sql query to get this result? ![alt text][1] [1]: /storage/temp/408-table1.jpg
group-by
table1.jpg (41.0 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Please describe how that matching should be done? Is it only per QtyFr=QtyTo or are there other conditions to meet?
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
You're not really looking to aggregate this information in a GROUP BY, but to order it appropriately and join that back together. I'm assuming that the ordering of the GradeTo and GradeFrom allows for something like this to take place: SELECT * FROM (SELECT mt.TYPE, mt.GradeTo, mt.Company, mt.QtyTo, ROW_NUMBER() OVER (ORDER BY mt.Type, mt.GradeTo DESC) AS Id FROM dbo.MyTable AS mt WHERE GradeFrom IS NULL AND QtyFr = 0 ) AS t1 JOIN (SELECT mt2.TYPE, mt2.GradeFrom, mt2.Company, mt2.QtyFr, ROW_NUMBER() OVER (ORDER BY mt2.Type, mt2.GradeFrom DESC) AS Id FROM dbo.MyTable AS mt2 WHERE GradeTo IS NULL AND QtyTo = 0 ) AS t2 ON t1.Id = t2.Id; Had to update it to also order by Type. You may even need to add Company there.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Not a problem. Happy to help. Remember, if something on the site is helpful, click the little "Thumbs Up" icon next to it. If an answer solves your problem, be sure to click the check box to make that the the preferred answer.
1 Like 1 ·
dmonk64 avatar image dmonk64 commented ·
Thanks Grant for this...this give me a better idea on how to approach this.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.