Solved

Difficult SQL query in SQL server 2005

Posted on 2013-05-27
21
380 Views
Last Modified: 2013-05-28
I need to extract data from a SQL server 2005 table
The table layout looks like this
C1 char(3)
C2 integer
C3 integer
.../...
The primary key is C1,C2
Each line in the table describe an object (a real !)
The same objet may appears several time
As the objet identifier is C3, this means there may be several couple (C1,C2) for the same C3
I need to extract from this table the list of objects for which the value of C1 is given but only those lines from the tables that correspond to the highest C1,C2 couples for which of course C1 is not the given value
I give an example
Table contains this C1,C2,C3)
(G12,1,345)
(G12,4,123)
(K09,1,123)
(L01,1,123)
(L01,2,345)
(L01,3,678)
The query result should be
(G12,1,345)
(K09,1,123)
C3=678 does not return anything because there is no 'previous' occurrence of it
C3 = 123 returns the last occurrence K09,1,123 (and not G12,4,123 which is "older")
C2 = 345 returns G12,1,345
0
Comment
Question by:LeTay
  • 10
  • 9
  • 2
21 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39199086
this doesn't meet you expected results, but it does produce this:
C1      C2      C3
L01      1      123
L01      2      345
not sure how K09,1,123 overridex L01,1,123 see http://sqlfiddle.com/#!3/2dffc/1
select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3 order by c1 desc, c2 desc) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
      ) as derived
where row_ref = 1 and c_ref > 1

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39199104
perhaps I should make the question clear...
why are these records not your expected results?
C1      C2      C3
L01      1      123
L01      2      345
0
 

Author Comment

by:LeTay
ID: 39199288
Maybe I forgot to mention that for my example value L01 is the given value of C1
So I need to find values (x,y,z) where x is not L01, z is somewhere specified in another row of the table and the x is the highest existing
In the example, the last row where z = 123 is found, not being the L01,.. row is K09,1,123
Etc...
Hello PortletPaul, does the query you propose give the expected result ?
0
 

Author Comment

by:LeTay
ID: 39199305
Hello PortletPaul,
I added the condition in the inside select (where C1 < 'L01') and ... it works !
I know SQL since ...35 years but your wonderful syntax I do not understand, despite the fact it works fine !
Can you please explain, so I will not die as an idiot ?!   ;-)
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39199515
Modified Paul's sql to meet your requirement.
select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3 order by c1 desc, c2 desc) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
      ) as derived
where row_ref = 2
order by c1,c2,c3

Open in new window


http://sqlfiddle.com/#!3/2dffc/7
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39199517
I removed the count(*) which I believe is not required.

http://sqlfiddle.com/#!3/2dffc/8
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39199929
>>I forgot to mention that for my example value L01 is the given value of C1
:) yes, that bit was what I could not understand.

Not sure I would use where C1 < 'L01'  here because it will prohibit the complete calculation of occurrences and I'm not sure we should assume that
> L01 does occur for each C3 value
> L01 occurs only once for each C3 value

So I would suggest the following:
select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3
                             order by
                              case when c1 = 'L01' then 'a' else c1 end DESC
                            , c2 DESC) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
      ) as derived
where row_ref = 1 and c_ref > 1

Open in new window

see http://sqlfiddle.com/#!3/2dffc/15
C1    C2    C3    ROW_REF  C_REF
K09    1    123    1       3
G12    4    123    2       3
L01    1    123    3       3

G12    1    345    1       2
L01    2    345    2       2

L01    3    678    1       1

Open in new window

Regarding explanation please see the above results of the inner subquery and then you will need to understand the nice advantages of "windowing functions". I have used 2 of these which are:

row_number() over (partition by ... order by .... )
count() over (partition by ...)

for more refer to "the over clause"

"partition by" is a little similar to "group by", basically this bit says do some calculations "within a cluster of records that share value(s)"

"order by" is I hope obvious

hence:
"row_number() over (partition by c3 ..."
will produce a unique integer value "within the cluster of records that share a c3 value" and then that unique number is provided according to the "order by"
e.g. K09,1,123 gets a row_number() of 1 and so on as shown in the table above.

nb: row_number() is considered a "ranking function" so it supports "order by"
below, count() is an "aggregate function" which does not support "order by"

For the count(*) over (partition by c3) this simply counts the occurrences of each c3 but supplies that value to each row "within the cluster" so you see 3 repeated 3 times and 2 repeated twice in the c_ref column in table above.

@Sharath, if L01 does not occur, or if L01 should occur more than once, for a C3 value then row_ref = 2 could be wrong I think.
0
 

Author Comment

by:LeTay
ID: 39200604
Hello PorletPaul,
Thanks for all these very interesting information
Now in fact I need a little bit more
I rewrote your select like this, adding the condition on C1 (see below)
What I need more is to join that select to the same Table in order, precisely, to get the C1 and C2 where C1 is the specific value
So (based on my first example), I need this
(G12,1,345,L01,2)
(K09,1,123,L01,1)
I have to write a join but have difficulty as the first select is a kind of "virtual" table
Can you help ?
Thanks

select
      c1
    , c2
    , c3
from (
        select
          c1
        , c2
        , c3
        , row_number() over (partition by c3
                             order by
                              case when c1 = 'L01' then 'a' else c1 end DESC
                            , c2 DESC) as row_ref
        , count(*) over (partition by c3) as c_ref
        from YourTable
        where C1 < 'SPECIFICVALUE'
      ) as derived
where row_ref = 1 and c_ref > 1
0
 

Author Comment

by:LeTay
ID: 39200704
Sorry, I found the way
I didn't realize that "derived" was the alias of that "virtual table"
Thank !
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200705
I believe you will need to treat the base table as 2 'virtual tables', by this I mean:

a. a selection of only 'L01' records
b. a selection excluding 'L01' records

then join a to b, but I think this only viable join is through C3

I think... I'll see what I can do

but: Do you want to filter by C1 or C3? seems more logical to me to search for 123 or 345
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:LeTay
ID: 39200717
Hello PortletPaul
Here is the solution I found
I wrote the select with the real tables and columns and it works
Thanks !
select A.Code_Vente,A.Lot_No,A.IUL,B.Code_Vente,B.Lot_No
from (select Code_Vente,Lot_No,IUL,
             row_number() over (partition by IUL order by Code_Vente desc, Lot_No Desc) as Row_Ref,
             count(*) over (partition by IUL) as c_ref
      from  Lots
      where Code_Vente < 'L09') as A,
      Lots B
where A.IUL        = B.IUL
and   B.Code_Vente = 'L09'
and   A.row_ref    = 1 and A.c_ref > 1
order by 1,2,3
0
 

Author Closing Comment

by:LeTay
ID: 39200720
Magic SQL queries !
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200748
try this please:
select
  coalesce(NL.C1, L.C1) C1
, coalesce(NL.C2, L.C2) C2
, coalesce(NL.C3, L.C3) C3
, L.C1 LC1
, L.C2 LC2
, L.C3 LC3
from (
      select * from yourTable where C1 = 'L01' 
     ) as L
left join (
            select
                c1
              , c2
              , c3
              , row_number() over (partition by c3
                                     order by c1 desc, c2 desc) as row_ref
            from yourTable where C1 <> 'L01' 
          ) as NL
                 on L.C3 = NL.C3
where NL.row_ref = 1

Open in new window

the left join & coalesce functions probably not really needed due to the where clause
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200754
this one I think
select
  NL.C1
, NL.C2
, NL.C3
, L.C1 LC1
, L.C2 LC2
, L.C3 LC3
from (
      select * from yourTable where C1 = 'L01' 
     ) as L
inner join (
            select
                c1
              , c2
              , c3
              , row_number() over (partition by c3
                                     order by c1 desc, c2 desc) as row_ref
            from yourTable where C1 <> 'L01' 
          ) as NL
                 on L.C3 = NL.C3
where NL.row_ref = 1

Open in new window

http://sqlfiddle.com/#!3/2dffc/22

The previous left join/coalesce query might be useful (not sure) you can see what it does without the where clause at the sqlfiddle url above.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200760
very good! well done!

just as a pointer, it's my view that overall its easier if we deal with the real tables/fields. It means you don't have to "translate" - and it really makes little difference at this end.

Cheers, Paul

"code_vente" ces't que le francias?
0
 

Author Comment

by:LeTay
ID: 39201045
Hello PortletPaul,
The select is related to public sale auctions (my customer)
C1 is the sale, C2 the object number (order of the sale) and C3 the object identifier
This select allows us to get the list of objects that were already presented in a previous sale and in fact to get the last one
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201101
interesting, did the last query work for you?
0
 

Author Comment

by:LeTay
ID: 39201254
which one precisely ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201278
ID: 39200754 (the most recent)
0
 

Author Comment

by:LeTay
ID: 39201287
Sorry, I didn't try
I used mine which works
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39201310
no problem. Cheers, Paul
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now