user7728035
Reputation:
Order by minimum values per group
I'm trying to order by PODID then CYID but sometimes a t.ID will have multiple PODIDs and/or CYIDs. All rows with the same t.ID should be together.
SELECT DISTINCT
t.ID AS TariffID,
t.TradeID,
pod.PODID,
cy.CYID
FROM
TB_Import_Tariff_Tier t (NOLOCK)
INNER JOIN
TB_Import_Tariff_Tier_POD_Group_History pod (NOLOCK) ON t.ID = pod.ID
INNER JOIN
TB_Import_Tariff_Tier_CY_Group_History cy (NOLOCK) ON t.ID = cy.TariffID
ORDER BY
pod.PODID, cy.CYID
Expected result:
TariffID TradeID PODID CYID
------------------------------
185 18 1 17939
185 18 1 17941
246 18 1 17939
246 18 1 17941
266 18 1 17939
266 18 1 17941
280 18 1 17939
280 18 1 17941
285 18 1 17939
252 18 1 17945
Edit: Sample was too small, here is the full result with Order By t.ID, pod.PODID, cy.CYID
+----------+---------+-------+-------+
| TariffID | TradeID | PODID | CYID |
+----------+---------+-------+-------+
| 251 | 18 | 4 | 18091 |
| 252 | 18 | 1 | 17945 |
| 254 | 18 | 4 | 17939 |
| 254 | 18 | 4 | 17941 |
| 255 | 18 | 4 | 17936 |
| 256 | 18 | 8 | 18091 |
| 257 | 18 | 4 | 18093 |
| 261 | 18 | 4 | 17939 |
| 261 | 18 | 4 | 17941 |
| 262 | 18 | 8 | 17936 |
| 263 | 18 | 4 | 18093 |
| 266 | 18 | 1 | 17939 |
| 266 | 18 | 1 | 17941 |
| 267 | 18 | 4 | 17936 |
| 268 | 18 | 4 | 18093 |
| 271 | 18 | 8 | 17939 |
| 271 | 18 | 8 | 17941 |
| 272 | 18 | 1 | 17936 |
| 273 | 18 | 4 | 18093 |
| 274 | 18 | 4 | 18091 |
| 274 | 18 | 4 | 18093 |
| 275 | 18 | 4 | 17939 |
| 275 | 18 | 4 | 17941 |
| 276 | 18 | 8 | 17936 |
| 277 | 18 | 4 | 18091 |
| 279 | 18 | 1 | 18091 |
| 279 | 18 | 1 | 18093 |
| 280 | 18 | 1 | 17939 |
| 280 | 18 | 1 | 17941 |
| 281 | 18 | 4 | 18093 |
| 284 | 18 | 8 | 18093 |
| 285 | 18 | 1 | 17939 |
| 286 | 18 | 1 | 18091 |
| 287 | 18 | 4 | 18093 |
| 290 | 18 | 4 | 18091 |
| 290 | 18 | 4 | 18093 |
| 291 | 18 | 4 | 18091 |
| 292 | 18 | 4 | 18093 |
| 293 | 18 | 4 | 17947 |
| 294 | 18 | 4 | 17945 |
| 295 | 18 | 8 | 17939 |
| 295 | 18 | 8 | 17941 |
| 296 | 18 | 4 | 18091 |
| 296 | 18 | 4 | 18093 |
| 297 | 18 | 1 | 17936 |
| 300 | 18 | 4 | 18093 |
| 301 | 18 | 4 | 17936 |
| 302 | 18 | 8 | 18091 |
| 303 | 18 | 4 | 18093 |
| 306 | 18 | 4 | 18091 |
| 306 | 18 | 4 | 18093 |
| 307 | 18 | 4 | 17936 |
| 308 | 18 | 8 | 18093 |
| 310 | 18 | 8 | 18093 |
| 311 | 18 | 4 | 18093 |
| 312 | 18 | 4 | 17939 |
| 312 | 18 | 4 | 17941 |
| 313 | 18 | 4 | 17936 |
| 316 | 18 | 4 | 18091 |
| 316 | 18 | 4 | 18093 |
| 317 | 18 | 1 | 17936 |
| 318 | 18 | 1 | 18091 |
+----------+---------+-------+-------+
All the PODID = 1 should be together first but if there's another PODID for the same t.ID they should be together.
If t.ID is at the end of the Order by it's still incorrect because the IDs are not together
+----------+---------+-------+-------+
| TariffID | TradeID | PODID | CYID |
+----------+---------+-------+-------+
| 272 | 18 | 1 | 17936 |
| 297 | 18 | 1 | 17936 |
| 317 | 18 | 1 | 17936 |
| 266 | 18 | 1 | 17939 |
| 280 | 18 | 1 | 17939 |
| 285 | 18 | 1 | 17939 |
| 266 | 18 | 1 | 17941 |
| 280 | 18 | 1 | 17941 |
| 252 | 18 | 1 | 17945 |
| 279 | 18 | 1 | 18091 |
| 286 | 18 | 1 | 18091 |
| 318 | 18 | 1 | 18091 |
| 279 | 18 | 1 | 18093 |
| 255 | 18 | 4 | 17936 |
| 267 | 18 | 4 | 17936 |
| 301 | 18 | 4 | 17936 |
| 307 | 18 | 4 | 17936 |
| 313 | 18 | 4 | 17936 |
| 254 | 18 | 4 | 17939 |
| 261 | 18 | 4 | 17939 |
| 275 | 18 | 4 | 17939 |
| 312 | 18 | 4 | 17939 |
| 254 | 18 | 4 | 17941 |
| 261 | 18 | 4 | 17941 |
| 275 | 18 | 4 | 17941 |
| 312 | 18 | 4 | 17941 |
| 294 | 18 | 4 | 17945 |
| 293 | 18 | 4 | 17947 |
| 251 | 18 | 4 | 18091 |
| 274 | 18 | 4 | 18091 |
| 277 | 18 | 4 | 18091 |
| 290 | 18 | 4 | 18091 |
| 291 | 18 | 4 | 18091 |
| 296 | 18 | 4 | 18091 |
| 306 | 18 | 4 | 18091 |
| 316 | 18 | 4 | 18091 |
| 257 | 18 | 4 | 18093 |
| 263 | 18 | 4 | 18093 |
| 268 | 18 | 4 | 18093 |
| 273 | 18 | 4 | 18093 |
| 274 | 18 | 4 | 18093 |
| 281 | 18 | 4 | 18093 |
| 287 | 18 | 4 | 18093 |
| 290 | 18 | 4 | 18093 |
| 292 | 18 | 4 | 18093 |
| 296 | 18 | 4 | 18093 |
| 300 | 18 | 4 | 18093 |
| 303 | 18 | 4 | 18093 |
| 306 | 18 | 4 | 18093 |
| 311 | 18 | 4 | 18093 |
| 316 | 18 | 4 | 18093 |
| 262 | 18 | 8 | 17936 |
| 276 | 18 | 8 | 17936 |
| 271 | 18 | 8 | 17939 |
| 295 | 18 | 8 | 17939 |
| 271 | 18 | 8 | 17941 |
| 295 | 18 | 8 | 17941 |
| 256 | 18 | 8 | 18091 |
| 302 | 18 | 8 | 18091 |
| 284 | 18 | 8 | 18093 |
| 308 | 18 | 8 | 18093 |
| 310 | 18 | 8 | 18093 |
+----------+---------+-------+-------+
Upvotes: 1
Views: 49
Answers (2)
Salman Arshad
Reputation: 272096
If I understand correctly, you need to order the data like this:
ORDER BY
MIN(PODID) OVER (PARTITION BY TariffID),
MIN(CYID) OVER (PARTITION BY TariffID),
TariffID,
PODID,
CYID
This will put TariffID groups with smallest PODID and CYID first, then sort within each TariffID by individual PODID, CYID:
| TariffID | TradeID | PODID | CYID |
|----------|---------|-------|-------|
| 272 | 18 | 1 | 17936 |
| 266 | 18 | 1 | 17939 |
| 266 | 18 | 1 | 17941 |
| 252 | 18 | 1 | 17945 |
| 255 | 18 | 4 | 17936 |
| 267 | 18 | 4 | 17936 |
| 254 | 18 | 4 | 17939 |
| 254 | 18 | 4 | 17941 |
| 261 | 18 | 4 | 17939 |
| 261 | 18 | 4 | 17941 |
| 275 | 18 | 4 | 17939 |
| 275 | 18 | 4 | 17941 |
| 251 | 18 | 4 | 18091 |
| 274 | 18 | 4 | 18091 |
| 274 | 18 | 4 | 18093 |
| 257 | 18 | 4 | 18093 |
| 263 | 18 | 4 | 18093 |
| 268 | 18 | 4 | 18093 |
| 273 | 18 | 4 | 18093 |
| 262 | 18 | 8 | 17936 |
| 271 | 18 | 8 | 17939 |
| 271 | 18 | 8 | 17941 |
| 256 | 18 | 8 | 18091 |
DB Fiddle for Short and Long Data
Upvotes: 1
GGadde
Reputation: 391
Add the t.ID in the ORDER BY clause. Did you try this? Or am I seeing this differently?
SELECT DISTINCT
t.ID AS TariffID,
t.TradeID,
pod.PODID,
cy.CYID
FROM
TB_Import_Tariff_Tier t (NOLOCK)
INNER JOIN
TB_Import_Tariff_Tier_POD_Group_History pod (NOLOCK) ON t.ID = pod.ID
INNER JOIN
TB_Import_Tariff_Tier_CY_Group_History cy (NOLOCK) ON t.ID = cy.TariffID
ORDER BY
t.ID,pod.PODID, cy.CYID
Upvotes: 1
Related Questions
- How to GROUP and choose lowest value in SQL
- Find minimum value in groups of rows
- T-Sql select and group by MIN()
- select MIN with order by
- How to GROUP and choose lowest value in SQL and associated column
- SQL Server : query grouping
- How to find the min, where TSQL groups by
- T-SQL ORDER BY base on MIN of a group's column
- How do you group by one column and retrieve a row with the minimum value of another column in T/SQL?
- Order by lowest value of one column grouped by another column