4

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

suraj gole
  • 43
  • 4
  • Can you alter tempimport table structure? If yes, add there some field, determining their records order (there is no such thing as order per se in SQL server) - can even be simple identity field. – Arvo Apr 01 '16 at 11:15
  • no, I can not alter table tempImport. – suraj gole Apr 01 '16 at 17:32
  • Then you are almost out of options. You may be able use first solution (using row physical location) from next thread: http://stackoverflow.com/questions/909155/equivalent-of-oracles-rowid-in-sql-server – Arvo Apr 04 '16 at 07:41

1 Answers1

1

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.

SQLmojoe
  • 1,924
  • 1
  • 11
  • 15
  • can't use this query I have more than 1000 records in tempImport – suraj gole Apr 04 '16 at 04:08
  • 1
    As indicated in my post, you can tweak the query to parameterize the case evaluations so it pulls the data from the column you want to follow the sort order of in tempImport instead of hard coding them in my sample. I'll write that example if I have time later but you should be able to find existing examples online. Regardless, these types of RBAR queries are not sustainable. Perf will eventually fall to the point it becomes unusable and you'll need an alternate solution anyway. Suggest exploring #1, modifying the original table or something else entirely. – SQLmojoe Apr 04 '16 at 17:09