July 1, 2010 at 7:13 am
Hello everyone who lands on this thread 🙂
Since i'm fairly new to set-based SQL, i cannot figure out why my SQL statement get a distinct out as a result :
SELECT DISTINCT Titel_BB.ISBN,
(SELECT TOP 1 Titel_BB.Druk_nr
FROM Titel_BB as ISBNSet
inner join dbo.Titel_Statussen as Statussen
on dbo.ActiefStatus(Titel_BB.Status,Titel_BB.Hist)=Statussen.ID
where ISBNSet.ISBN=Titel_BB.ISBN
order by Statussen.Volgorde desc)
as Druk_nr1
FROM Titel_BB
to give a bit of explanation to this code : i need to get the distinct out of 1 table from all isbn's (books), however i need to figure out what status the most recent is of the same ISBN.
The function dbo.ActiefStatus is just to replace some statusses according to the specs written out.
The Table Titel_Statussen is used to determine the order of importance (numeric)
I hope this makes a bit of sence, been chewing on this for a couple of days now :/
TIA
July 1, 2010 at 7:21 am
Can you provide table layout, sample data and expected results. It's easier than us guessing. Check out the first link in my signature to help with posting to this site
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2010 at 8:12 am
My apologies about being too restrictive about my question.
I'll start with the 2 table definitions :
First the Titel_BB table :
CREATE TABLE [dbo].[titel_bb](
[druk_nr] [int] NOT NULL,
[vvers_dat] [datetime] NOT NULL,
[status] [smallint] NOT NULL,
[isbn] [varchar](13) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
[hist] [char](1) COLLATE SQL_Latin1_General_CP850_CI_AS NULL,
[creatie__dat] [datetime] NULL CONSTRAINT [DF_titel_bb_creatie__dat] DEFAULT (getdate()),
CONSTRAINT [PK_titel_bb] PRIMARY KEY CLUSTERED
(
[druk_nr] ASC,
[isbn] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Filling of this table :
Insert into Titel_BB(Druk_nr,status,isbn,hist)
SELECT 1,3,3095561991130,'J'
UNION ALL
SELECT 2,3,3095561991130,'N'
UNION ALL
SELECT 1,3,3095561991131,'N'
UNION ALL
SELECT 1,3,3095561991132,'N'
UNION ALL
SELECT 1,3,3095561991133,'N'
UNION ALL
SELECT 1,3,3095561991134,'N'
UNION ALL
SELECT 5,2,3095561991134,'N'
UNION ALL
SELECT 1,3,3095561991136,'N'
UNION ALL
SELECT 1,3,3095561991137,'N';
second table is just to reflect priorities :
CREATE TABLE [dbo].[Titel_Statussen](
[ID] [int] NOT NULL,
[Omschrijving] [varchar](30) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
[Volgorde] [int] NOT NULL,
[Actief] [bit] NOT NULL,
CONSTRAINT [PK_Titel_Statussen] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Filling of this table :
Insert into Titel_Statussen(ID,Omschrijving,Volgorde,Actief)
SELECT 1,'In produktie',2,1
UNION ALL
SELECT 2,'Verschenen',1,1
UNION ALL
SELECT 3,'In Herdruk',3,1
UNION ALL
SELECT 4,'Uitverkocht',4,1
UNION ALL
SELECT 5,'Zal niet meer verschijnen',5,1
UNION ALL
SELECT 6,'Te bestellen bij uitgever',6,1
UNION ALL
SELECT 7,'in prijs opgeheven',7,1
UNION ALL
SELECT 8,'herdruk in overweging',8,1
UNION ALL
SELECT 9,'printing on demand',9,1;
The Function i use in the SQL statement in first post :
CREATE FUNCTION [dbo].[ActiefStatus] (@Status int, @Hist char(1)) RETURNS INT AS BEGIN
IF @Hist='J'
return 6
ELSE IF @status = 8
return 3
ELSE IF @status = 9
return 2
return @status
END
What i expect is the DISTINCT result of the query, Instead the result gives me all records
July 1, 2010 at 8:38 am
I think this is what you are looking for, but not sure.
SELECT ISBN, max(dbo.ActiefStatus(Status,Hist)) Status
from Titel_BB
group by ISBN
order by ISBN DESC
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2010 at 9:14 am
Do you want the most recent status to display? Which column defines that it is a most recent status? vvers_dat or creatie__dat or something else?
July 1, 2010 at 9:48 am
Eugene Elutin (7/1/2010)
Do you want the most recent status to display? Which column defines that it is a most recent status? vvers_dat or creatie__dat or something else?
That is correct, from 2 of the same isbn numbers, i need the one with the most active status to be chosen in the result.
the columns druk_nr (highest nr) and Status (which is why the table Titel_Statussen is used to order by in conjunction with SELECT TOP (1) as an inner join)
July 1, 2010 at 9:57 am
I still don't understand what you are looking for for results. Is it
ISBN Status
30955619911373
30955619911363
30955619911343
30955619911333
30955619911323
30955619911313
30955619911306
because that is the highest status the function returns or are you looking for
ISBN Status
30955619911372
30955619911362
30955619911345
30955619911332
30955619911322
30955619911312
30955619911302
because this has the highest Volgorde from the statussen table? This is what I was looking for when I asked for expected results
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2010 at 10:11 am
Mike01 (7/1/2010)
I still don't understand what you are looking for for results. Is it
ISBN Status30955619911373
30955619911363
30955619911343
30955619911333
30955619911323
30955619911313
30955619911306
because that is the highest status the function returns or are you looking for
ISBN Status30955619911372
30955619911362
30955619911345
30955619911332
30955619911322
30955619911312
30955619911302
because this has the highest Volgorde from the statussen table? This is what I was looking for when I asked for expected results
I'm not looking for highest status in the end result. The exact problem i got, is that instead of giving me on of the 2 results you stated, it gives me the complete recordset, so 3095561991130 and 3095561991134 as isbn would return double instead of distincted.
it's especially one of the same isbn numbers that needs to be validated in what the inner join does.
so this section of the code:
SELECT TOP 1 Titel_BB.Druk_nr
FROM Titel_BB as ISBNSet
inner join dbo.Titel_Statussen as Statussen
on dbo.ActiefStatus(Titel_BB.Status,Titel_BB.Hist)=Statussen.ID
where ISBNSet.ISBN=Titel_BB.ISBN
order by Statussen.Volgorde desc
should be the logic to determine which isbn number is distincted
I hope i make some sense here, english is not my native language :/
July 1, 2010 at 10:13 am
lionwelp (7/1/2010)
Eugene Elutin (7/1/2010)
Do you want the most recent status to display? Which column defines that it is a most recent status? vvers_dat or creatie__dat or something else?That is correct, from 2 of the same isbn numbers, i need the one with the most active status to be chosen in the result.
the columns druk_nr (highest nr) and Status (which is why the table Titel_Statussen is used to order by in conjunction with SELECT TOP (1) as an inner join)
Cool!. There is a small difference between most RECENT and most ACTIVE status, is'n it? What defines "the most ACTIVE status"? Is it highest Volgorde value in your Titel_Statussen table?
I guess you want most RECENT record with the most ACTIVE ("important") status. Am I right?
Try:
;WITH RecActStatus
AS
(
SELECT bb.isbn, bb.druk_nr, ss.Omschrijving, ss.Volgorde
,ROW_NUMBER() OVER (PARTITION BY bb.isbn ORDER BY ss.Volgorde DESC, bb.druk_nr DESC ) as HS
FROM dbo.titel_bb bb
JOIN dbo.Titel_Statussen ss
ON ss.ID = CASE
WHEN bb.hist = 'J' THEN 6
WHEN bb.[status] = 8 THEN 3
WHEN bb.[status] = 9 THEN 2
ELSE bb.[status]
END
)
SELECT isbn, druk_nr, Omschrijving, Volgorde
FROM RecActStatus
WHERE HS = 1
July 1, 2010 at 10:22 am
again, I don't know what you are expecting for results. I can keep posting tables of what I think you expect, but if you don't tell me, I can only keep guessing. Can you provide a table of what you are expecting for results? Here's my latest attempt at guessing.
ISBN Druk_nr
30955619911302
30955619911311
30955619911321
30955619911331
30955619911345
30955619911361
30955619911371
here's the code that produced it
SELECT Titel_BB.ISBN, max(druk_nr) Druk_nr
from Titel_BB inner join dbo.Titel_Statussen
on dbo.ActiefStatus(Titel_BB.Status,Titel_BB.Hist)= Titel_Statussen.ID
group by Titel_BB.ISBN
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 1, 2010 at 10:26 am
What is vvers_dat supposed to be? I'm running your code on my sandbox and I can't do the data insert because that column disallows NULLs and has no insert value in your test data.
July 1, 2010 at 10:42 am
Honestly, I think you're overly complicating things. You don't need the function, if I'm reading your code correctly. You can just do a CASE statement in the JOIN clause.
Also, am I reading correctly that you want the most recent Druk_nr value? The one with the greatest (most recent) creatie__dat?
Here's the code I have (using no function and a different subquery). It only returns 1 row because there is no default value on the CASE statement. You need to fix that with whatever you deem appropriate or the code won't work:
SELECT DISTINCT ISBNSet.ISBN, ISBNSet.Druk_nr
FROM Titel_BB AS ISBNSet
INNER JOIN dbo.Titel_Statussen AS Statussen
ON (CASE WHEN ISBNSet.Hist = 'J' THEN 6 WHEN ISBNSet.Hist <> 'J' and ISBNSet.Status = 8 THEN 3
WHEN ISBNSet.Hist <> 'J' and ISBNSet.Status = 8 THEN 2 END) = Statussen.ID
--Needs an ELSE clause in this statement. What is the default value you want returned if no conditions are returned?
INNER JOIN (SELECT Druk_nr, Max(creatie__dat) as creatie__dat
FROM Titel_BB
GROUP BY Druk_nr) titel_bb
ON ISBNSet.Druk_nr = titel_bb.Druk_nr
AND ISBNSet.creatie__dat = titel_bb.creatie__dat
July 1, 2010 at 10:42 am
Mike01 (7/1/2010)
again, I don't know what you are expecting for results. I can keep posting tables of what I think you expect, but if you don't tell me, I can only keep guessing. Can you provide a table of what you are expecting for results? Here's my latest attempt at guessing.
ISBN Druk_nr30955619911302
30955619911311
30955619911321
30955619911331
30955619911345
30955619911361
30955619911371
here's the code that produced it
SELECT Titel_BB.ISBN, max(druk_nr) Druk_nrfrom Titel_BB inner join dbo.Titel_Statussen
on dbo.ActiefStatus(Titel_BB.Status,Titel_BB.Hist)= Titel_Statussen.ID
group by Titel_BB.ISBN
That is the result i'm after. At the moment i'm not behind my dev-pc, but i surely give this code a spin.
The 'big picture' is that i need to encapsulate this result and insert it into another table.
Thank you a lot for the patience and time you spend on it !
I'll let a note if it is workeable into the insert statement.
July 1, 2010 at 10:56 am
Eugene Elutin (7/1/2010)
Cool!. There is a small difference between most RECENT and most ACTIVE status, is'n it? What defines "the most ACTIVE status"? Is it highest Volgorde value in your Titel_Statussen table?
I guess you want most RECENT record with the most ACTIVE ("important") status. Am I right?
Try:
;WITH RecActStatus
AS
(
SELECT bb.isbn, bb.druk_nr, ss.Omschrijving, ss.Volgorde
,ROW_NUMBER() OVER (PARTITION BY bb.isbn ORDER BY ss.Volgorde DESC, bb.druk_nr DESC ) as HS
FROM dbo.titel_bb bb
JOIN dbo.Titel_Statussen ss
ON ss.ID = CASE
WHEN bb.hist = 'J' THEN 6
WHEN bb.[status] = 8 THEN 3
WHEN bb.[status] = 9 THEN 2
ELSE bb.[status]
END
)
SELECT isbn, druk_nr, Omschrijving, Volgorde
FROM RecActStatus
WHERE HS = 1
Thanks for replying 🙂
You're correct, the validation got the most recent Druk_nr and lowest number of Status (like isbn 3095561991134 got 2 rows, one with druk_nr= 1,status=3 and druk_nr=5,Status=2. the one i need in this case would be the one with druk_nr=5,status=2)
The code would be great, however like i said to Mike01; the 'big picture' is to encapsulate an insert over this to copy the distinct results into a new table, which i don't see how i could do that with a CTE.
July 1, 2010 at 11:09 am
Brandie Tarvin (7/1/2010)
Honestly, I think you're overly complicating things. You don't need the function, if I'm reading your code correctly. You can just do a CASE statement in the JOIN clause.Also, am I reading correctly that you want the most recent Druk_nr value? The one with the greatest (most recent) creatie__dat?
I want to have the most recent druk_nr and the one with the lowest number of the Status.ID table
However the code you propose looks really interesting to try out, i'll look into it when i'm back at the office behind my dev-pc.
Thanks for replying and looking into things (and yes, i'm aware i'm overcomplicating stuff, but that's because i'm still try to get a grip on CTE's, derived tables and nested inner joins 🙂 )
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply