February 17, 2010 at 8:10 am
I have a table with the following structure
CREATE TABLE [RECON_RECORD] (
[Serial] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Amount] [float] NOT NULL ,
[Type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DESCRIPTION] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [PK_RECON_RECORD] PRIMARY KEY CLUSTERED
(
[Serial]
) ON [PRIMARY]
) ON [PRIMARY]
GO
I inserted in it the following data
117612.69CRCenter house20919700001001 2590
217612.69CRCenter house20919700001001 2590
317612.69CRCenter house20919700001001 2590
417612.69CRBall 20919700001001 2590
517612.69CReggs 20919700001001 2590
617612.69CRflower 20919700001001 2590
617612.69CRApple 20919700001001 2590
I want when select distinct DESCRIPTION to get the following rersults
Center house
Ball
eggs
flower
Apple
what I get is
Apple
Ball
Center house
eggs
flower
what shall I do to get the results I want
February 17, 2010 at 8:11 am
It looks like the only difference is the sequence. Is that correct? If so, you'll need to specify how you want it ordered, otherwise SQL just gives you whatever it comes up with.
- 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
February 17, 2010 at 8:19 am
As GSquared said, See you have an Identity column it gives the order you entered until unless you specify what order you want result set.
EnjoY!
February 17, 2010 at 1:37 pm
GSquared (2/17/2010)
It looks like the only difference is the sequence. Is that correct? If so, you'll need to specify how you want it ordered, otherwise SQL just gives you whatever it comes up with.
could u suggest me how to change the order please
February 17, 2010 at 2:04 pm
You could add "ORDER BY MIN(Serial)" to your SELECT clause.
February 17, 2010 at 3:05 pm
lmu92 (2/17/2010)
You could add "ORDER BY MIN(Serial)" to your SELECT clause.
Serial --> is not included in my select
Select description from x
any other idea please
February 17, 2010 at 3:12 pm
ali.mahmoud.habib (2/17/2010)
lmu92 (2/17/2010)
You could add "ORDER BY MIN(Serial)" to your SELECT clause.Serial --> is not included in my select
Select description from x
any other idea please
You might not like it but I still recommend using the previous solution, since the order by column does not have to be included in your select statement to work. However, if you don't like it, you don't have to use it... 😉
Here's a s(i/a)mple (= simple sample):
DECLARE @t TABLE (id INT , val VARCHAR(10))
INSERT INTO @t
SELECT 1,'c' UNION ALL
SELECT 1,'c' UNION ALL
SELECT 2,'a'
SELECT val
FROM @t
GROUP BY val
ORDER BY MIN(id)
/* result set
val
c
a
*/
February 17, 2010 at 9:26 pm
lmu92 (2/17/2010)
ali.mahmoud.habib (2/17/2010)
lmu92 (2/17/2010)
You could add "ORDER BY MIN(Serial)" to your SELECT clause.Serial --> is not included in my select
Select description from x
any other idea please
You might not like it but I still recommend using the previous solution, since the order by column does not have to be included in your select statement to work. However, if you don't like it, you don't have to use it... 😉
Here's a s(i/a)mple (= simple sample):
DECLARE @t TABLE (id INT , val VARCHAR(10))INSERT INTO @t
SELECT 1,'c' UNION ALL
SELECT 1,'c' UNION ALL
SELECT 2,'a'
SELECT val
FROM @t
GROUP BY val
ORDER BY MIN(id)
/* result set
val
c
a
*/
It worked very well thanks a lot for the help
February 17, 2010 at 11:28 pm
Depending how your mind works, you might find this solution more intuitive:
-- Test table
CREATE TABLE #ReconRecord
(
serial TINYINT PRIMARY KEY,
amount FLOAT NOT NULL,
[type] VARCHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);
GO
-- Sample data
INSERT #ReconRecord (serial, amount, type, description) VALUES (1, 17612.69, 'CR', N'Center house');
INSERT #ReconRecord (serial, amount, type, description) VALUES (2, 17612.69, 'CR', N'Center house');
INSERT #ReconRecord (serial, amount, type, description) VALUES (3, 17612.69, 'CR', N'Center house');
INSERT #ReconRecord (serial, amount, type, description) VALUES (4, 17612.69, 'CR', N'Ball');
INSERT #ReconRecord (serial, amount, type, description) VALUES (5, 17612.69, 'CR', N'Eggs');
INSERT #ReconRecord (serial, amount, type, description) VALUES (6, 17612.69, 'CR', N'Flower');
INSERT #ReconRecord (serial, amount, type, description) VALUES (7, 17612.69, 'CR', N'Apple');
GO
-- This returns DISTINCT values for description, in serial number order
-- (the serial number used is the MINimum present for each DISTINCT)
SELECT RR1.description
FROM #ReconRecord RR1
WHERE RR1.serial =
(
SELECT MIN(RR2.serial)
FROM #ReconRecord RR2
WHERE RR2.description = RR1.description
)
ORDER BY
RR1.serial ASC;
GO
-- Tidy up
DROP TABLE #ReconRecord;
-- End script
Not better than Lutz's fine effort, just a different way of expressing the requirement.
(I confess I like this solution mostly because of the cool Segment + Top operator combination in the query plan)
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 1:42 am
GT-897544 (2/17/2010)
As GSquared said, See you have an Identity column it gives the order you entered until unless you specify what order you want result set.EnjoY!
If i understand you correctly this is wrong, If you want your result set in a specific order you have to use order by. Period. An identity column in no way represents the order that rows will be returned.
February 18, 2010 at 3:26 am
Dave Ballantyne (2/18/2010)
GT-897544 (2/17/2010)
As GSquared said, See you have an Identity column it gives the order you entered until unless you specify what order you want result set.If I understand you correctly this is wrong, If you want your result set in a specific order you have to use order by. Period. An identity column in no way represents the order that rows will be returned.
This is interesting. The numbers allocated to the identity column do reflect the order in which rows were added to the table, if the rows were added one at a time. Otherwise, the values in the identity column just reflect the order in which SQL Server happened to allocate those identity values.
That said, the fact that the column with the identity property ('Serial') forms the clustered index gives no guarantee of ordering - as any good SQL person should know. On the other hand, for such a small table, it is exceedingly unlikely that SQL Server would in fact return the rows in any other order, in this example. The counterpoint is that such behaviour is not in fact guaranteed.
If GT-897544 is aware of all that, it is possible that he or she was attempting to explain the apparent order that Ali reported in his original question. I propose to give GT-897544 the benefit of the doubt. A better-worded reply would have made that clearer though...
Technically, the idea that to guarantee ordering you must use an explicit ORDER BY clause on a SELECT statement is entirely correct - otherwise the query relies on behaviour which might appear to be reliable, but which could in fact change on the next run, or with the next product update.
I post this as a general comment - I know Dave is well aware of all that.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 8:07 am
That's not entirely true though, if you SET IDENTITY_INSERT ON, you can force identity values that are out of sequence:
CREATE TABLE #temp (iRow int identity(1,1),something char(1))
INSERT INTO #temp
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c' UNION ALL
SELECT 'd'
SET IDENTITY_INSERT #temp ON
INSERT INTO #temp (iRow,something) VALUES (1,'e')
SET IDENTITY_INSERT #temp OFF
INSERT INTO #temp
SELECT 'f' UNION ALL
SELECT 'g' UNION ALL
SELECT 'h' UNION ALL
SELECT 'i'
SELECT * FROM #temp
**Edited to add additional rows after SET IDENTITY_INSERT ON used, to show that it does not affect the value of the next identity used.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
February 18, 2010 at 10:05 am
jcrawf02 (2/18/2010)
That's not entirely true though, if you SET IDENTITY_INSERT ON, you can force identity values that are out of sequence:
Interesting point - thanks for sharing.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 18, 2010 at 10:15 am
Also, in this case, using the serial number does not guarantee the order of flower and apple, since the serial is 6 for both records.
February 18, 2010 at 10:17 am
Dan Guzman - Not the MVP (2/18/2010)
Also, in this case, using the serial number does not guarantee the order of flower and apple, since the serial is 6 for both records.
I took that to be an error on the OP's part - corrected in the solution I posted earlier, after Lutz's 🙂
Come to think of it, both the original post and my script defined a PRIMARY KEY on the IDENTITY column, so the duplicated sizes must be an error - and the particular IDENTITY_INSERT value jcrawf02 used would produce an error too. Yay!
edit: added link
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply