SQL QUERY : multiple records against one column value . need to compare another column value to fetch one record

Question

A sample table us like

STATUS        INVOICE
=======================
processed          100
reconciled         100
reconciled         200
paid               300
paid               100
paid               200

Output should be

STATUS        INVOICE
=======================
processed          100
reconciled         200
paid               300

Logic : If there are multiple statuses against an invoice number , then we should follow the below order to fetch . Processed > reconciled > paid

Please help me with the SQL query statement for this requirement .

Hi, and welcome to Stackoverflow! You should give the solution a try and update your question with some code that you've developed and a specific problem with your code that you need help with. — squillman, Nov 09 '18 at 13:25
What if there is one more record for processed? Processed with value of 200 — WhoamI, Nov 09 '18 at 13:25
If there are more records for processed, then the requirement is to fetch all records having processed — Asutosh Mohapatra, Nov 09 '18 at 13:38
SO is not a free coding service website. Post your query. If it doesn't work, we can help. — Eric, Nov 09 '18 at 17:21

score 0 · Answer 1 · answered Nov 09 '18 at 13:24

0

This is a prioritization query. You can handle it using row_number():

select t.*
from (select t.*,
             row_number() over (partition by invoice
                                order by case status when 'Processed' then 1 when 'reconciled' then 2 when  'paid' then 3 else 4 end
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

answered Nov 09 '18 at 13:24

Gordon Linoff

1,242,037
58
646
786

I am sorry to ask this question ..I am very much new to SQL world ..Can you please let me know if I need to swap t with the table name? – Asutosh Mohapatra Nov 09 '18 at 13:48
@AsutoshMohapatra . . . You can put the table name before the `t`, so the `t` is the alias. – Gordon Linoff Nov 09 '18 at 14:16

score 0 · Answer 2 · answered Nov 09 '18 at 13:25

You need conditional ordering with row_number() :

select top (1) with ties t.*
from table t
order by row_number() over (partition by invoice 
                            order by (case status 
                                           when 'Processed' 
                                           then 1 
                                           when 'reconciled' 
                                           then 2 
                                           when 'paid' 
                                           then 3 
                                           else 4 
                                      end)

                           );

jean · Answer 3 · 2018-11-09T13:51:11.793

Others answers are ok but I'm posting it for the cases there's hundres (or more) of categories because in this case populating a table variable is better than writing lots and lots of CASE statements.

The trick here is to populate a table variable or temp table with your ordering rules and just aggregate by it.

create table dbo.[SAMPLE]
(
   [STATUS] varchar(30) not null
  ,[INVOICE] int not null
)
GO

insert into dbo.[SAMPLE]
values
 ('processed',    100)
,('reconciled',   100)
,('reconciled',   200)
,('paid',         300)
,('paid',         100)
,('paid',         200)
GO

The below is a partial result showing how it get grouped by your ordering rules

--Processed > reconciled > paid
declare @Ordering as table 
(
   [STATUS] varchar(30) not null
  ,[Order] smallint not null
)
insert into @Ordering
values
 ('processed', 3)
,('reconciled',2)
,('paid',1)

    select sp.[INVOICE], max(ord.[Order]) as Precedence
    from dbo.[SAMPLE] sp
    join @Ordering ord on ord.[STATUS] = sp.[STATUS]
    group by sp.[INVOICE]

and below the final query with the expected results

--Processed > reconciled > paid
declare @Ordering as table 
(
   [STATUS] varchar(30) not null
  ,[Order] smallint not null
)
insert into @Ordering
values
 ('processed', 3)
,('reconciled',2)
,('paid',1)


select ord.[STATUS], grouped.INVOICE
from
(
    select sp.[INVOICE], max(ord.[Order]) as Precedence
    from dbo.[SAMPLE] sp
    join @Ordering ord on ord.[STATUS] = sp.[STATUS]
    group by sp.[INVOICE]
) as grouped
join @Ordering ord on ord.[Order] = grouped.Precedence

It can be also a interesting solution from performance perspective (acid test required of course).

score 0 · Answer 4 · answered Nov 09 '18 at 13:53

if you have a status table and the order of status like this

id    desc
1     processed
2     reconcilied
3     paid

the better way is joining with this tatble, group by invoice and select max(id)

select i.invoice, max(s.id)
from status s left outer join invoice i
on s.desc = i.status
group by i.invoice

if you havn't this table you can use with to create a virtual table and do this or you can use the case then

https://modern-sql.com/feature/with

https://learn.microsoft.com/it-it/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

score 0 · Answer 5 · answered Nov 09 '18 at 14:09

You can try this.

DECLARE @SampleDate TABLE (STATUS VARCHAR(20), INVOICE INT)
INSERT INTO @SampleDate  VALUES
('processed', 100),
('reconciled', 100),
('reconciled', 200),
('paid', 300),
('paid', 100),
('paid', 200)

SELECT STATUS, INVOICE FROM (
    SELECT T.*,  ROW_NUMBER() OVER(PARTITION BY INVOICE ORDER BY St.ID) AS RN FROM @SampleDate T
        INNER JOIN (VALUES (1,'processed'), (2,'reconciled'), (3,'paid')) St(Id, Name) ON T.STATUS = St.Name
) AS X WHERE RN= 1

Result:

STATUS               INVOICE
-------------------- -----------
processed            100
reconciled           200
paid                 300

score 0 · Answer 6 · edited Nov 09 '18 at 14:17

0

WITH TempData As (SELECT MAX(INVOICE) AS INVOICE, [STATUS], CASE WHEN [STATUS] = 'processed' THEN 1 WHEN [STATUS] = 'reconciled' THEN 2 WHEN [STATUS] = 'paid' THEN 3 ELSE 4 END AS SEQ
  FROM SAMPLETEST GROUP BY [STATUS])
  SELECT  [STATUS], INVOICE FROM TempData ORDER BY TempData.SEQ;

edited Nov 09 '18 at 14:17

Serkan Arslan

13,158
4
29
44

answered Nov 09 '18 at 14:13

Sachin Gupta

1

SQL QUERY : multiple records against one column value . need to compare another column value to fetch one record

6 Answers6

Linked