An SQL puzzle
Check out this little snippet of SQL:
[sourcecode language="sql"]
SELECT PRAMvtID,
PRAMvtRALoadDteTme,
PRAMvtRAStatus,
PRAMvtRAType,
PRAMvtRAApprvlStatus,
PRAMvtCompany,
PRAMvtDeliveryNetwrk,
PRAMvtMeasurePoint,
PRAMvtSalesMonth,
PRAMvtTicketNo
INTO #complete_tkts
FROM PAPRAMovement d
WHERE PRAMvtID IN (SELECT PRAMvtID
FROM PAPRAMovement dd
WHERE dd.PRAMvtCompany = d.PRAMvtCompany
AND dd.PRAMvtDeliveryNetwrk =
d.PRAMvtDeliveryNetwrk
AND dd.PRAMvtMeasurePoint =
d.PRAMvtMeasurePoint
AND dd.PRAMvtSalesMonth = d.PRAMvtSalesMonth
AND dd.PRAMvtTicketNo = d.PRAMvtTicketNo
AND dd.PRAMvtRAStatus = 'C' )
[/sourcecode]
When my friend showed me this snippet, i was a little amused. I asked why was the extra nesting required when he was using the same table to compare? He could have might as well gone with this:
[sourcecode language="sql" highlight="13"]
SELECT PRAMvtID,
PRAMvtRALoadDteTme,
PRAMvtRAStatus,
PRAMvtRAType,
PRAMvtRAApprvlStatus,
PRAMvtCompany,
PRAMvtDeliveryNetwrk,
PRAMvtMeasurePoint,
PRAMvtSalesMonth,
PRAMvtTicketNo
INTO #complete_tkts
FROM PAPRAMovement
WHERE PRAMvtRAStatus = 'C'
[/sourcecode]
But thats when he told me that, the query was designed in that manner for a reason and he was not able to figure it out.
Thats when i actually sat up and started to pay attention. After 5 mins of close inspection of the snippet, i realized that this was a brilliantly designed query to perform grouping of the PRAMvtIDs in the following group order of the table coloumns:
- PRAMvtCompany
- PRAMvtDeliveryNetwrk
- PRAMvtMeasurePoint
- PRAMvtSalesMonth
- PRAMvtTicketNo
Now one might ask, why not use a simple GroupBy instead. This argument grows stronger when u realise that the grouping with this query will not be a perfect cascade. The grouping will take place in a random fashion based on the way the above 5 values are encountered in the table row by row. What i am getting at is that the data will not be ordered, but it will be grouped.
So the question remains... why not the group? For one, if this query was to be used in Linq, then it would return a List. But if one were to use the Groupby, then what you would get is a Dictionary. So, i guess it is a pure matter of choice. It was an interesting query. It taught me yet again that most things in life should have a purpose, for their existence.