CODEWITHSUNDEEP
sql-server-2008
suraj gole
suraj gole

Reputation: 43

Select records without sorting in sql server

I have one table TempImport and the selected query result is as display is image. But I want to get result in order by code as order present in tempImport table using SQL Server

the select query is:

SELECT DISTINCT Code,
    (dd.dd_receipt_no)
FROM   TempImport tm WITH(NOLOCK)
    INNER JOIN depositor_mast dm WITH(NOLOCK)
            ON  tm.Code = dm.dm_code
    INNER JOIN deposit_detl dd WITH(NOLOCK)
            ON  dm.dm_srno = dd.dd_dm_srno
WHERE  dd.dd_dt_code IN ('PDFP', 'PDFD', 'PDFE')
    AND tm.Remark = 'OK'
    AND dm.dm_dep_ind = 'PDEP'
    AND dd.dd_delind = 'L'
    AND dd.dd_exit_date = 0
    AND (dd.Certificate_No = '' OR dd.Certificate_No IS NULL)

Image shows TempImport , Selected Result and Required Result

Upvotes: 4

Views: 388

Answers (1)

SQLmojoe
SQLmojoe

Reputation: 2053

You're effectively asking for a custom sort order. There are a few ways to go about this, none are pretty IMHO. 1. Since you can't modify the original table, you can create a secondary table (regular or temp table) with a column for what you want to sort by and a sort order column. That's an alternate approach to what Arvo suggested but it works around you restriction on not modifying tempImport. However, it means extra space, maintenance, etc...

  1. You can write a RBAR query that iterates through the values of your custom sort order, in this case it's the Code column in the tempImport table. Works fine with a small number of rows, pain increases to the point the query is unusable when your table gets very big. Essentially, your query will look something like

SELECT a,b,c FROM ......<your stuff here>.... ...<more of your stuff>... ORDER BY CASE a WHEN 'G05198' THEN 1 WHEN 'K10739' THEN 2 WHEN 'B11737' THEN 3 ELSE a END

You can tweak this using parameters to make it more robust but the basic premise doesn't change: you're dealing row-by-agonizing-row so do expect perf pains as your dataset grows. Perhaps this is enough to buy you some time to do get the powers that be to modify the schema so you can be rid of this.

Upvotes: 1

Related Questions