Help with conditional query

Transact-SQL
1

Hello!

I can’t find a solution for my query problem.
Maybe someone can help me of you professionals.

I have the following query result from several tables:

LSID STATUS Name Ersteller Erstelldatum
1 1 start NULL NULL
4 3 Testrezeptur Thomas Lisker 2010-01-14 08:28:42.000
4 2 Testrezeptur Thomas Lisker 2010-01-14 08:28:42.000
5 2 Universalreiniger Horst Ehmann 2010-01-14 13:38:38.000
5 3 Universalreiniger Horst Ehmann 2010-01-14 13:38:38.000
7 3 Duschbad Alexander Liska 2010-01-14 14:32:57.000
7 2 Duschbad Alexander Liska 2010-01-14 14:32:57.000
8 2 Duschbad Horst Ehmann 2010-01-14 14:38:03.000
8 3 Duschbad Horst Ehmann 2010-01-14 14:38:03.000
9 2 Testcreme Franz Obig 2010-01-14 16:25:22.000
9 3 Testcreme Franz Obig 2010-01-14 16:25:22.000
10 3 Testcreme Franz Obig 2010-01-14 16:23:41.000
2004 3 Cremedusche Milch & Honig Gerda Dank 2016-02-01 15:30:16.000
2004 4 Cremedusche Milch & Honig Gerda Dank 2016-02-01 15:30:16.000
2005 3 Cremedusche Mandel Christine Jäger 2016-02-01 15:34:21.000
2006 3 Cremedusche Aloe Vera Josef Brückl 2016-02-01 16:04:36.000
2006 4 Cremedusche Aloe Vera Josef Brückl 2016-02-01 16:04:36.000

There may be two different or more records for an LSID that only differ in the Status field.
The values for status can be 1-4.

Whenever an LSID appears twice or more times in the query I want:

  1. if one of the records has the status 1 or 2, select the smaller one and not any of the others
    AND if this is not the case, then additionally
  2. Always select the record that has the larger status if the status is 3 or 4.

I have tried in countless ways to solve the problem, but I always fail...
I ask for your help and am grateful for every answer !!!

Mfg,
specklinsen

2

Try this:

select lsid
      ,status
      ,name
      ,ersteller
      ,erstelldatum
  from (select lsid
              ,status
              ,name
              ,ersteller
              ,erstelldatum
              ,row_number() over(partition by lsid
                                 order by case status
                                             when 4 then 3
                                             when 3 then 4
                                             else status
                                          end
                                )
               as rn
          from yourtable
       ) as a
 where rn=1
;
3

thanks man, will try it on monday or tuesday and then give reply :smiley:

regards,
specklinsen