CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Hisanth
Hisanth

Reputation: 62

How to use SUBSTRING With Distinct in sql server 2008

My query is

select distinct PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
order by  PoNo

It shows like as follows order

But I need PO0001, Po0002, PO0003, PO0004.....

So, i am using 

select distinct PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
order by SUBSTRING(PoNo,5,1)

But it shows an error like

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Help me

Upvotes: 2

Views: 92

Answers (3)

Akshey Bhat
Akshey Bhat

Reputation: 8545

SELECT PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
GROUP BY PoNo, PoDate, SupplierId, BillNo
ORDER BY Cast(SUBSTRING(PoNo, 3, len(PoNo)) as int)

Extract the number from PoNo and orderding by that number.

Upvotes: 0

Bhavesh Harsora
Bhavesh Harsora

Reputation: 693

You can also try following one:

SELECT PoNo, PoDate, SupplierId, BillNo
FROM (
    select distinct CAST(SUBSTRING(PoNo,2,LEN(PoNo)-2) AS INT) AS Pid, PoNo, PoDate, SupplierId, BillNo
    FROM Purchase_Product
) AS tbl
order by  Pid

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use GROUP BY instead:

SELECT PoNo, PoDate, SupplierId, BillNo
FROM Purchase_Product
GROUP BY PoNo, PoDate, SupplierId, BillNo
ORDER BY SUBSTRING(PoNo, 5, 1)

Upvotes: 4

Related Questions