January 18, 2011 at 2:38 pm
Hi,
I tried for a query, for which I couldn't get the logic. Could you please guide me an approach to solve to get following output.
I have a column with values like 4,10,45,23,11,10,0,44 in Table1.
Now my output should be two columns in which
column1: 0,4,10,11,23,44,45 (which is usual distinct values in Asc order)
Column2: 4,10,11,23,44,45,NULL
For each value in column1, the corresponding column2 value is the next highest of value in column1.
If I am not clear plz write the two values in columns then you may get it. and for the last value(here it is 45) in col1 there is no next highest, so null should be displayed.
Plz help on this.Thanks in advance
January 18, 2011 at 2:47 pm
Use a cte to create a row_Number() expression to number your rows in the correct order. Then link the table to itself to find its row, and the previous row, using a full outer join.
If you'd like a more specific example, see the first link in my signature on how to make consumable test data to facilitate us writing code to create your solution.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 19, 2011 at 12:42 pm
Craig Farrell (1/18/2011)
If you'd like a more specific example, see the first link in my signature on how to make consumable test data to facilitate us writing code to create your solution.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 20, 2011 at 6:45 am
Just in case, "LEFT PUTER JOIN" is a typo. Should be "LEFT OUTER JOIN".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2011 at 7:36 am
Dear Celko,
Thank you So much. I got a clear idea now on the row_number() function.
Thanks again.
January 20, 2011 at 7:54 am
Dear Celko,
Back with one more doubt. In your query your column 'i' is a PK. So no duplicates.
But the column in which i have use has duplicate values. Though I used Distinct I couldn't avoid duplicates. My query is
/*
use northwind
with productstwo AS(
Select Distinct Unitprice,row_number() OVER(Order BY unitprice) AS Row FROM Products)
Select P1.Unitprice 'FROM',P2.Unitprice 'To'
FROM Productstwo p1 Left Outer JOIN Productstwo p2
ON p2.Row=p1.row+1
*/
Thanks In Advance
January 20, 2011 at 10:37 am
CELKO (1/20/2011)
Since a table must have a primary key to a table, I automatically put one in the skeleton schemas I post.ROW_NUMBER() will, effectively, sort the list, then give each row a unique number, so duplicates will be "unique-ified"; in your case, you will get (a,a) in the result set from your "non-table" example. SELECT DISTINCT is expensive, so avoid it whenever possible.
Where does this requirement comes from?
I just tried to create a table without a PK. And guess what: It worked!
So your "must have" at most can be a "should have".
As a side note: I added a unique check constraint that also reject NULL values, of course... 😉
January 20, 2011 at 11:01 am
LutzM (1/20/2011)
CELKO (1/20/2011)
Since a table must have a primary key to a table, I automatically put one in the skeleton schemas I post.ROW_NUMBER() will, effectively, sort the list, then give each row a unique number, so duplicates will be "unique-ified"; in your case, you will get (a,a) in the result set from your "non-table" example. SELECT DISTINCT is expensive, so avoid it whenever possible.
Where does this requirement comes from?
I just tried to create a table without a PK. And guess what: It worked!
So your "must have" at most can be a "should have".
As a side note: I added a unique check constraint that also reject NULL values, of course... 😉
You can create a table that doesn't have a Primary Key constraint, but if it doesn't actually have any set of columns that uniquely identify the rows, then it's not really a relational table, by definition. To be relational, rows have to be unique. Doesn't mean it's not a dataset, just means it's not a relational table. SQL works just fine with datasets, except where duplicate rows cause unusual issues with ACIDity.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2011 at 11:53 am
GSquared (1/20/2011)
...You can create a table that doesn't have a Primary Key constraint, but if it doesn't actually have any set of columns that uniquely identify the rows, then it's not really a relational table, by definition. To be relational, rows have to be unique. Doesn't mean it's not a dataset, just means it's not a relational table. SQL works just fine with datasets, except where duplicate rows cause unusual issues with ACIDity.
Where is the definition that only a PK can be used to uniquely identify a row?
As you may have noticed, I (purposely) mentioned the unique check constraint excluding NULL values. Since this will ensure the data being unique, it's still a relational table, isn't it?
I know Codds rule #2 specifically use the term "primary key". I also know that there can be more than one unique check constraint, but only one PK. But assuming a db model being in 3rd NF, I just can't think of an example to have more than one totally independent unique check constraint.
Edit:
Don't get me wrong... I'm not arguing against PK. Not at all. I'm just trying to make the point that there is an alternative to ensure rows being unique... Not really recommending it though.
January 20, 2011 at 7:17 pm
CELKO (1/19/2011)
CREATE TABLE Foobar(i INTEGER NOT NULL PRIMARY KEY);
WITH Sequenced_Foobar (i, seq)
AS
(SELECT i, ROW_NUMBER() OVER(ORDER BY i))
SELECT S1.i, S2.i
FROM Sequenced_Foobar AS S1
LEFT PUTER JOIN
Sequenced_Foobar AS S2
ON S2.seq = S1.seq +1;
Nicely done, Joe. Straight to the point. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2011 at 6:27 am
LutzM (1/20/2011)
GSquared (1/20/2011)
...You can create a table that doesn't have a Primary Key constraint, but if it doesn't actually have any set of columns that uniquely identify the rows, then it's not really a relational table, by definition. To be relational, rows have to be unique. Doesn't mean it's not a dataset, just means it's not a relational table. SQL works just fine with datasets, except where duplicate rows cause unusual issues with ACIDity.
Where is the definition that only a PK can be used to uniquely identify a row?
As you may have noticed, I (purposely) mentioned the unique check constraint excluding NULL values. Since this will ensure the data being unique, it's still a relational table, isn't it?
I know Codds rule #2 specifically use the term "primary key". I also know that there can be more than one unique check constraint, but only one PK. But assuming a db model being in 3rd NF, I just can't think of an example to have more than one totally independent unique check constraint.
Edit:
Don't get me wrong... I'm not arguing against PK. Not at all. I'm just trying to make the point that there is an alternative to ensure rows being unique... Not really recommending it though.
There are multiple means of physically implementing row uniqueness. But "the primary key" != "primary key constraint". You have a set of columns that uniquely identify the row. You may or may not have a defined "primary key constraint" on the table, but you still have a "primary key". It's a question of definition, not a question of implementation.
You can have a heap with no constraints, no indexes (clustered or otherwise), all columns defined as SQLVariant, columns named "Col1","Col2", and if some combination of those columns can be used to uniquely identify the rows, then you have a table because you have a "primary key". You aren't enforcing it in any way, but it's still in there.
On the other hand, you can define a set of rows as the "primary key" using a "primary key constraint", and then violate it and have duplicate rows, and you won't have a relational "table".
It's a terminology thing, not an engineering thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 21, 2011 at 6:46 am
GSquared (1/21/2011)
...There are multiple means of physically implementing row uniqueness. But "the primary key" != "primary key constraint". You have a set of columns that uniquely identify the row. You may or may not have a defined "primary key constraint" on the table, but you still have a "primary key". It's a question of definition, not a question of implementation.
You can have a heap with no constraints, no indexes (clustered or otherwise), all columns defined as SQLVariant, columns named "Col1","Col2", and if some combination of those columns can be used to uniquely identify the rows, then you have a table because you have a "primary key". You aren't enforcing it in any way, but it's still in there.
On the other hand, you can define a set of rows as the "primary key" using a "primary key constraint", and then violate it and have duplicate rows, and you won't have a relational "table".
It's a terminology thing, not an engineering thing.
I expected from the very beginning we don't have a different point of view on this subject. 😀
I've just been picky about the "must have a primary key" statement Joe made earlier. I just couldn't express myself clear enough. :crying: Thank you for making my point more clear.
January 21, 2011 at 10:00 am
CELKO (1/21/2011)
There is a good story about Dr. Codd, ANSI, Primary keys, UNIQUE and NULLs.The short version is that the PRIMARY KEY came from the need to have sequential files (read:mag tapes) in some sorted order. Dr. Codd put it in for that reason -- hey, RDBMS was still being invented at this point and everyone was still thinking in file systems and sequences. Do fish think of water? It got into SQL about the time that Codd came back and realized that a key is a key and no key is better than another.
ANSI X3H2 considered making PRIMARY KEY a requirement, instead of a shorthand for NOT NULL UNIQUE and a default for REFERENCE'S. It got voted down because the committee members had products built on existing file systems which allowed duplicate records. Only Teradata had a system that removed redundant dups; they had to go back and screw up things to be compliant. No good deed goes unpunished.
Isn't that somewhat contradictory to your previous statement?
Since a table must have a primary key ...
Actually, your recent post does help a lot to understand why rule #2 is defined as it is. Thanks for posting it, Joe.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply