Back devices: row

Showing posts with label row. Show all posts

Friday, March 30, 2012

How can I retrieve a recordset from a matrix-like table?

I have a table defined as (int Row, int Column, money Data); as it were a matrix.

Which is the more efficient way of retrieving a result set with the following form?

Column1 Column2 Column3
---- ---- ----
Data11 Data12 Data13
Data21 Data22 Data23
Data31 Data32 Data33
... ... ...

Thanks a lot in advance.Lookup "Crosstab queries" in Books Online, and you will see a perfect example of how to do what you want to do.

How can I resolve this database error, in VS 2005?

Every time I try to add a new row to my table, i get this error which i don't now what it means and how i can correct it, could you please advice. i am using VS 2005 and VB Language

**************The error message

"No row was updated.

The data in row 2 was not committed.

Error source:mscorlib.

Error Message: Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index"

What code are you using to add the row?

This is the basic VB syntax

Dim myNewRow as DataRow

myNewRow=MyDataTable.NewRow()

|||

Yep. What kind of table? A table in a sql server database, or a datatable, which is a .net class?

|||

i am using sql server database

How can I request row #3 in a dataset?

I've got a table that houses the data for several routes, (routeID, pointID, Longitude, Latitude and Elevation). a set of Points make up a route. I'd like to programmatically access specific points and I'm trying to figure out how to request...say the third point in my dataset. I'm new to SQL, but I was able to figure out that I can find the row number by using the SQL syntax:

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, ElevationFROM [PointTable]WHERE (RouteID = 5)

But I cannot (or do not know how to) add a clause that says

AND (Num = 3)

So can someone show me how to request a specific row?

It's really easy, and the trick to it will serve you in good stead in many other situations.

When you issue a select statement, you are selecting "from" something. That something might be a table, or it might be a view.

But what IS a view? It's a query that's given a name, an alias if you will. When a view is used, the actual query statement it represents is substituted for the view's name in the sql statement as part of the parsing process.

How does that knowledge help?

Because you don't have to bother to give a view a name, you can just fill in a query for yourself. Just pop in inside a set of parentheses and supply an alias

So:

SELECT *

FROM

(

SELECT ROW_NUMBER()OVER(ORDER by PointID)as'Num', Latitude, Longitude, Elevation
FROM [PointTable]
WHERE (RouteID = 5)

) AS temporary_result_set

WHERE temporary_result_set.Num = 3

|||WOW! Thank you David, that did the trick. You're explaination just doubled my understanding on how T-SQL works and got my mind thinking of other things to try.

How can I remove a duplicate row?

I have a table that has two rows in it that are identical and need to
delete one of them. Since any kind of where clause in a delete
statement will hit both of them, how can I remove one of these rows?
TIAPick the dups by grouping and put them in a temp table. remove all
duplicate records from the parent table and insert the groped
duplicates (in this table, you should have one occourance of each
record) from the temp table into this old table that dosent have any
rows of these duplicate records.
Hope I didnt loose you with my wording.. ..! (It would help if you had
given some DDL code...!|||http://www.aspfaq.com/2431
Now how about having a key and/or other constraints to prevent this from
happening again?
http://www.aspfaq.com/2509
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:7pgav09liocekdk7rdgd2leo1gnuct58n3@.4ax.com...
> I have a table that has two rows in it that are identical and need to
> delete one of them. Since any kind of where clause in a delete
> statement will hit both of them, how can I remove one of these rows?
> TIA|||On 24 Jan 2005 11:08:44 -0800, "QueryBuilder" <pg.242w@.gmail.com>
wrote:
>Pick the dups by grouping and put them in a temp table. remove all
>duplicate records from the parent table and insert the groped
>duplicates (in this table, you should have one occourance of each
>record) from the temp table into this old table that dosent have any
>rows of these duplicate records.
>Hope I didnt loose you with my wording.. ..! (It would help if you had
>given some DDL code...!
This makes sense. I was hoping I could remove the extra in place. (My
thinking is that if the DB knows there are two rows there must be
someway that it uniquely identifies them.)|||Matthew Speed <mspeed@.mspeed.net> wrote in
news:7pgav09liocekdk7rdgd2leo1gnuct58n3@.4ax.com:
> I have a table that has two rows in it that are identical and need to
> delete one of them. Since any kind of where clause in a delete
> statement will hit both of them, how can I remove one of these rows?
> TIA
set rowcount 1
delete from t1
where col1=1 and col2=1|||On Mon, 24 Jan 2005 14:08:04 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:
>http://www.aspfaq.com/2431
>Now how about having a key and/or other constraints to prevent this from
>happening again?
The keys are now in place. This was a client database I was hired to
do some work on. When I saw the problem my first thought was to add
constraints but one can't implement a unique constraint unless the
existing data is already unique.
>http://www.aspfaq.com/2509|||"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:cjmav0dg4s0uki574285s1rekljfod4lfa@.4ax.com...
> On Mon, 24 Jan 2005 14:08:04 -0500, "Aaron [SQL Server MVP]"
> <ten.xoc@.dnartreb.noraa> wrote:
> >http://www.aspfaq.com/2431
> >
> >Now how about having a key and/or other constraints to prevent this
from
> >happening again?
> The keys are now in place. This was a client database I was hired
to
> do some work on. When I saw the problem my first thought was to add
> constraints but one can't implement a unique constraint unless the
> existing data is already unique.
I add identitiy columns to tables all the time to get rid of dups. You
can leave the identitiy column around or simply remove it after you
are done. Another technique is to create a new table as select
distinct * from xxx, drop the old one and rename the new one as
appropriately.|||> I add identitiy columns to tables all the time to get rid of dups.
With sensible design, you won't have dupes at all. Does your data not have
a candidate key? Do you not use primary keys for any reason?|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVkkXtmAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> > I add identitiy columns to tables all the time to get rid of dups.
> With sensible design, you won't have dupes at all. Does your data
not have
> a candidate key? Do you not use primary keys for any reason?
Data is often loaded in a database before being transformed and
duplicates removed. This is what happens in the real world.|||> Data is often loaded in a database before being transformed and
> duplicates removed. This is what happens in the real world.
The real world? What is that? Glad my job is inside a vacuum, where we
load data into a staging table, insert the NEW, NON-DUPLICATE rows into our
production tables, then blow away or archive the staging data. (Rather than
just throwing everything into our production tables, and removing duplicates
there.)|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uHq19RnAFHA.2572@.TK2MSFTNGP10.phx.gbl...
> > Data is often loaded in a database before being transformed and
> > duplicates removed. This is what happens in the real world.
> The real world? What is that? Glad my job is inside a vacuum,
where we
> load data into a staging table
Now, think about your "staging" table and the tables I am talking
about. Is the light coming on yet?|||> Now, think about your "staging" table and the tables I am talking
> about. Is the light coming on yet?
Funny that you removed the rest of my description, which kind of usurps your
petty insult. Grow up.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eBY9JUoAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> > Now, think about your "staging" table and the tables I am talking
> > about. Is the light coming on yet?
> Funny that you removed the rest of my description, which kind of
usurps your
> petty insult. Grow up.
Sorry if I struck a nerve. I only responded to your post in the same
"tone" that was directed at me. Concerning the part of your post that
was redacted it was simply irrelevant. If you still don't get it I'm
not sure I can dumb it down any further. Sorry :(
--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||> was redacted it was simply irrelevant. If you still don't get it I'm
> not sure I can dumb it down any further. Sorry :(
Oh christ. You know what everybody? I am sick and tired of helping people
to end up dealing with this childish crap. I have better things to do with
my time than to be at the wrong end of someone's childish recess attacks.
See ya.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ebL2Z7oAFHA.1400@.TK2MSFTNGP11.phx.gbl...
> > was redacted it was simply irrelevant. If you still don't get it
I'm
> > not sure I can dumb it down any further. Sorry :(
> Oh christ. You know what everybody? I am sick and tired of helping
people
> to end up dealing with this childish crap. I have better things to
do with
> my time than to be at the wrong end of someone's childish recess
attacks.
> See ya.
You're actually trying to help?
Here's some helpful advice.
If you're going to post with this attitude of condescension, at least
get your facts right. You might consider thinking a bit more about the
questions and replys before posting. You also seem to enjoy putting
people on the defensive and when you can't get your way you end up
lashing out like a child.
I'll let you get you the last word in since you seem intent on
"helping" me until I give up.|||/*select duplicate rows
select count(trnno), trnno from dw_ndls_train
group by trnno
having count(trnno) > 1
/*del duplicate rows
delete from dw_ndls_train
where (trnno, trname)
not in
( select min(trnno), trname
from dw_ndls_train group by trname
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

How can I remove a duplicate row?

Wednesday, March 28, 2012

How can I refactor these GROUP BY queries to be faster?

(SQL Server 2000, SP3a)
I'm trying to construct a VIEW that will return *one* row per DossierUNID,
and the row should be the DossierUNID row with the lowest SortOrder value.
However, I have some cases where SortOrder is NULL (which should be treated
as the lowest). Additionally, some SortOrder values may be *duplicated*.
Consider the following representative data:
if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
[dbo].[#Education]
create table [dbo].[#Education]
(
[RowUNID] uniqueidentifier,
[DossierUNID] uniqueidentifier,
[SortOrder] int,
[TStamp] timestamp
)
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'3C2CE763-DAF8-46CD-8B09-67979CA68325',
'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'C98A6838-2523-4507-A4F3-FFC6FAD51329',
'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'885B1138-E13A-4D87-8F5A-B8C379B1F328',
'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'EC70574F-6470-4348-B471-4DE3E81D402C',
'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'D0C43744-B59A-48BC-99ED-61A9D759261C',
'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'D5E90FED-3023-470B-ADA2-4F809B746F81',
'8B359795-73CE-469C-B797-5AD55A9B023A', 1
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'446C0422-2F90-4B01-957C-9ADA2669F385',
'8B359795-73CE-469C-B797-5AD55A9B023A', 2
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
'8B359795-73CE-469C-B797-5AD55A9B023A', 3
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'591C60B8-23A8-4D01-A519-3515A1032951',
'8B359795-73CE-469C-B797-5AD55A9B023A', 4
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'0EEED645-27B0-459C-B964-BF1B5BCB35C0',
'5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder]) select
'1544DF52-F93A-4871-86C9-E67DB976BDB7',
'5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
I have a couple of queries that yield the results that I'm looking for:
select e.*
from (
select [RowUNIDChecksum] = min(binary_checksum(e.[RowUNID]))
from (
select [DossierUNID] = e.[DossierUNID],
[SortOrder] = min(isnull(e.[SortOrder],
0))
from [dbo].[#Education] as e with (nolock)
group by e.[DossierUNID]
) as q
inner join [dbo].[#Education] as e with (nolock) on
e.[DossierUNID] = q.[DossierUNID]
and
isnull(e.[SortOrder], 0) = q.[SortOrder]
group by e.[DossierUNID]
) as q
inner join [dbo].[#Education] as e with (nolock) on
binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
Or:
select e.*
from [dbo].[#Education] as e with (nolock)
inner join (
select [DossierUNID] = e.[DossierUNID],
[Key] = min(right(replicate('0', 3) +
convert(varchar(3), isnull(e.[SortOrder], 0)), 3) + convert(varchar(36),
e.[RowUNID]))
from [dbo].[#Education] as e with (nolock)
group by e.[DossierUNID]
) as q on right(q.[Key], 36) = e.[RowUNID]
But I'm wondering if there's a better (read: more performant ;-) way to
accomplish this.
Thanks for any help anyone can provide! :-)
John PetersonOK, what about that ?
> I'm trying to construct a VIEW that will return *one* row per DossierUNID,
> and the row should be the DossierUNID row with the lowest SortOrder value.
Select RowUNID, MIN(ISNULL(SortOrder,-1))
FROM #Education
GROUP BY RowUNID
HTH, Jens SUessmeyer.
--
http://www.sqlserver2005.de
--
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> (SQL Server 2000, SP3a)
> I'm trying to construct a VIEW that will return *one* row per DossierUNID,
> and the row should be the DossierUNID row with the lowest SortOrder value.
> However, I have some cases where SortOrder is NULL (which should be
> treated as the lowest). Additionally, some SortOrder values may be
> *duplicated*.
> Consider the following representative data:
> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
> [dbo].[#Education]
> create table [dbo].[#Education]
> (
> [RowUNID] uniqueidentifier,
> [DossierUNID] uniqueidentifier,
> [SortOrder] int,
> [TStamp] timestamp
> )
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'EC70574F-6470-4348-B471-4DE3E81D402C',
> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '446C0422-2F90-4B01-957C-9ADA2669F385',
> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '591C60B8-23A8-4D01-A519-3515A1032951',
> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
> select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
> I have a couple of queries that yield the results that I'm looking for:
> select e.*
> from (
> select [RowUNIDChecksum] = min(binary_checksum(e.[RowUNID]))
> from (
> select [DossierUNID] = e.[DossierUNID],
> [SortOrder] = min(isnull(e.[SortOrder],
> 0))
> from [dbo].[#Education] as e with (nolock)
> group by e.[DossierUNID]
> ) as q
> inner join [dbo].[#Education] as e with (nolock) on
> e.[DossierUNID] = q.[DossierUNID]
> and
> isnull(e.[SortOrder], 0) = q.[SortOrder]
> group by e.[DossierUNID]
> ) as q
> inner join [dbo].[#Education] as e with (nolock) on
> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
> Or:
> select e.*
> from [dbo].[#Education] as e with (nolock)
> inner join (
> select [DossierUNID] = e.[DossierUNID],
> [Key] = min(right(replicate('0', 3) +
> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) + convert(varchar(36),
> e.[RowUNID]))
> from [dbo].[#Education] as e with (nolock)
> group by e.[DossierUNID]
> ) as q on right(q.[Key], 36) = e.[RowUNID]
>
> But I'm wondering if there's a better (read: more performant ;-) way to
> accomplish this.
> Thanks for any help anyone can provide! :-)
> John Peterson
>|||Sorry, Jens -- I probably wasn't clear: I want one row per DossierUNID (not
RowUNID). You'll notice that the RowUNIDs are unique, and there are
multiple DossierUNIDs in the table. See my sample output with those queries
for the results that I'm hoping to achieve.
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:evhI12YUFHA.612@.TK2MSFTNGP12.phx.gbl...
> OK, what about that ?
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
> Select RowUNID, MIN(ISNULL(SortOrder,-1))
> FROM #Education
> GROUP BY RowUNID
> HTH, Jens SUessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> (SQL Server 2000, SP3a)
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> However, I have some cases where SortOrder is NULL (which should be
>> treated as the lowest). Additionally, some SortOrder values may be
>> *duplicated*.
>> Consider the following representative data:
>> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
>> [dbo].[#Education]
>> create table [dbo].[#Education]
>> (
>> [RowUNID] uniqueidentifier,
>> [DossierUNID] uniqueidentifier,
>> [SortOrder] int,
>> [TStamp] timestamp
>> )
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'EC70574F-6470-4348-B471-4DE3E81D402C',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '446C0422-2F90-4B01-957C-9ADA2669F385',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '591C60B8-23A8-4D01-A519-3515A1032951',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> I have a couple of queries that yield the results that I'm looking for:
>> select e.*
>> from (
>> select [RowUNIDChecksum] =>> min(binary_checksum(e.[RowUNID]))
>> from (
>> select [DossierUNID] = e.[DossierUNID],
>> [SortOrder] =>> min(isnull(e.[SortOrder], 0))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> e.[DossierUNID] = q.[DossierUNID]
>> and
>> isnull(e.[SortOrder], 0) = q.[SortOrder]
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
>> Or:
>> select e.*
>> from [dbo].[#Education] as e with (nolock)
>> inner join (
>> select [DossierUNID] = e.[DossierUNID],
>> [Key] = min(right(replicate('0', 3) +
>> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) + convert(varchar(36),
>> e.[RowUNID]))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q on right(q.[Key], 36) = e.[RowUNID]
>>
>> But I'm wondering if there's a better (read: more performant ;-) way to
>> accomplish this.
>> Thanks for any help anyone can provide! :-)
>> John Peterson
>>
>|||Ok, sorry messed up with your explanation, so you want one one per
DossierUNID, you have 4 different DossierUNIDs:
--
Select count(*), DossierUNID
From #Education
Group by DossierUNID
DossierUNID
-- --
2 5548776F-6DEE-4AEA-8CD4-4108D331BE63
4 8B359795-73CE-469C-B797-5AD55A9B023A
6 A552B76B-FCD7-4D7C-BE5E-AA17A730904B
3 CFD039FC-EAF4-4F0B-B6DB-CB143948CE26
--
You should get the result that query (or did i missed a thing in your
description)
Select DossierUNID, MIN(ISNULL(SortOrder,-1))
FROM #Education
GROUP BY DossierUNID
DossierUNID
-- --
5548776F-6DEE-4AEA-8CD4-4108D331BE63 -1
8B359795-73CE-469C-B797-5AD55A9B023A 1
A552B76B-FCD7-4D7C-BE5E-AA17A730904B 1
CFD039FC-EAF4-4F0B-B6DB-CB143948CE26 -1
Jens Suessmeyer.
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:eJy2s7YUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Sorry, Jens -- I probably wasn't clear: I want one row per DossierUNID
> (not RowUNID). You'll notice that the RowUNIDs are unique, and there are
> multiple DossierUNIDs in the table. See my sample output with those
> queries for the results that I'm hoping to achieve.
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:evhI12YUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> OK, what about that ?
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> Select RowUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY RowUNID
>> HTH, Jens SUessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> (SQL Server 2000, SP3a)
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> However, I have some cases where SortOrder is NULL (which should be
>> treated as the lowest). Additionally, some SortOrder values may be
>> *duplicated*.
>> Consider the following representative data:
>> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
>> [dbo].[#Education]
>> create table [dbo].[#Education]
>> (
>> [RowUNID] uniqueidentifier,
>> [DossierUNID] uniqueidentifier,
>> [SortOrder] int,
>> [TStamp] timestamp
>> )
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'EC70574F-6470-4348-B471-4DE3E81D402C',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '446C0422-2F90-4B01-957C-9ADA2669F385',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '591C60B8-23A8-4D01-A519-3515A1032951',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> I have a couple of queries that yield the results that I'm looking for:
>> select e.*
>> from (
>> select [RowUNIDChecksum] =>> min(binary_checksum(e.[RowUNID]))
>> from (
>> select [DossierUNID] = e.[DossierUNID],
>> [SortOrder] =>> min(isnull(e.[SortOrder], 0))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> e.[DossierUNID] = q.[DossierUNID]
>> and
>> isnull(e.[SortOrder], 0) = q.[SortOrder]
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
>> Or:
>> select e.*
>> from [dbo].[#Education] as e with (nolock)
>> inner join (
>> select [DossierUNID] = e.[DossierUNID],
>> [Key] = min(right(replicate('0', 3) +
>> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) + convert(varchar(36),
>> e.[RowUNID]))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q on right(q.[Key], 36) = e.[RowUNID]
>>
>> But I'm wondering if there's a better (read: more performant ;-) way to
>> accomplish this.
>> Thanks for any help anyone can provide! :-)
>> John Peterson
>>
>>
>|||Yes -- but now I want to somehow *relate* that result set to the appropriate
record, in toto, in the original table. If the SortOrder is duplicated, we
can pick one at random (though, ideally, I would want the one with the max
TStamp value).
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OZsVxFZUFHA.4056@.TK2MSFTNGP15.phx.gbl...
> Ok, sorry messed up with your explanation, so you want one one per
> DossierUNID, you have 4 different DossierUNIDs:
> --
> Select count(*), DossierUNID
> From #Education
> Group by DossierUNID
> DossierUNID
> -- --
> 2 5548776F-6DEE-4AEA-8CD4-4108D331BE63
> 4 8B359795-73CE-469C-B797-5AD55A9B023A
> 6 A552B76B-FCD7-4D7C-BE5E-AA17A730904B
> 3 CFD039FC-EAF4-4F0B-B6DB-CB143948CE26
> --
> You should get the result that query (or did i missed a thing in your
> description)
>
> Select DossierUNID, MIN(ISNULL(SortOrder,-1))
> FROM #Education
> GROUP BY DossierUNID
> DossierUNID
> -- --
> 5548776F-6DEE-4AEA-8CD4-4108D331BE63 -1
> 8B359795-73CE-469C-B797-5AD55A9B023A 1
> A552B76B-FCD7-4D7C-BE5E-AA17A730904B 1
> CFD039FC-EAF4-4F0B-B6DB-CB143948CE26 -1
>
> Jens Suessmeyer.
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:eJy2s7YUFHA.3176@.TK2MSFTNGP12.phx.gbl...
>> Sorry, Jens -- I probably wasn't clear: I want one row per DossierUNID
>> (not RowUNID). You'll notice that the RowUNIDs are unique, and there are
>> multiple DossierUNIDs in the table. See my sample output with those
>> queries for the results that I'm hoping to achieve.
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
>> in message news:evhI12YUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> OK, what about that ?
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> Select RowUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY RowUNID
>> HTH, Jens SUessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> (SQL Server 2000, SP3a)
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> However, I have some cases where SortOrder is NULL (which should be
>> treated as the lowest). Additionally, some SortOrder values may be
>> *duplicated*.
>> Consider the following representative data:
>> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
>> [dbo].[#Education]
>> create table [dbo].[#Education]
>> (
>> [RowUNID] uniqueidentifier,
>> [DossierUNID] uniqueidentifier,
>> [SortOrder] int,
>> [TStamp] timestamp
>> )
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'EC70574F-6470-4348-B471-4DE3E81D402C',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '446C0422-2F90-4B01-957C-9ADA2669F385',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '591C60B8-23A8-4D01-A519-3515A1032951',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> I have a couple of queries that yield the results that I'm looking for:
>> select e.*
>> from (
>> select [RowUNIDChecksum] =>> min(binary_checksum(e.[RowUNID]))
>> from (
>> select [DossierUNID] = e.[DossierUNID],
>> [SortOrder] =>> min(isnull(e.[SortOrder], 0))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> e.[DossierUNID] = q.[DossierUNID]
>> and
>> isnull(e.[SortOrder], 0) = q.[SortOrder]
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
>> Or:
>> select e.*
>> from [dbo].[#Education] as e with (nolock)
>> inner join (
>> select [DossierUNID] = e.[DossierUNID],
>> [Key] = min(right(replicate('0', 3) +
>> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) +
>> convert(varchar(36), e.[RowUNID]))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q on right(q.[Key], 36) = e.[RowUNID]
>>
>> But I'm wondering if there's a better (read: more performant ;-) way
>> to accomplish this.
>> Thanks for any help anyone can provide! :-)
>> John Peterson
>>
>>
>>
>|||ok, getting nearer ;-)
What about that one:
Select E.* from #Education AS E
INNER JOIN
(
Select DossierUNID, min(TStamp) TStamp,
MIN(SortOrder) SortOrder
FROM #Education
GROUP BY DossierUNID
) Subquery
ON Subquery.DossierUNID = E.DossierUNID AND
Subquery.TStamp = E.TStamp
"John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
news:ejM70JZUFHA.2768@.tk2msftngp13.phx.gbl...
> Yes -- but now I want to somehow *relate* that result set to the
> appropriate record, in toto, in the original table. If the SortOrder is
> duplicated, we can pick one at random (though, ideally, I would want the
> one with the max TStamp value).
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OZsVxFZUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>> Ok, sorry messed up with your explanation, so you want one one per
>> DossierUNID, you have 4 different DossierUNIDs:
>> --
>> Select count(*), DossierUNID
>> From #Education
>> Group by DossierUNID
>> DossierUNID
>> -- --
>> 2 5548776F-6DEE-4AEA-8CD4-4108D331BE63
>> 4 8B359795-73CE-469C-B797-5AD55A9B023A
>> 6 A552B76B-FCD7-4D7C-BE5E-AA17A730904B
>> 3 CFD039FC-EAF4-4F0B-B6DB-CB143948CE26
>> --
>> You should get the result that query (or did i missed a thing in your
>> description)
>>
>> Select DossierUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY DossierUNID
>> DossierUNID
>> -- --
>> 5548776F-6DEE-4AEA-8CD4-4108D331BE63 -1
>> 8B359795-73CE-469C-B797-5AD55A9B023A 1
>> A552B76B-FCD7-4D7C-BE5E-AA17A730904B 1
>> CFD039FC-EAF4-4F0B-B6DB-CB143948CE26 -1
>>
>> Jens Suessmeyer.
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eJy2s7YUFHA.3176@.TK2MSFTNGP12.phx.gbl...
>> Sorry, Jens -- I probably wasn't clear: I want one row per DossierUNID
>> (not RowUNID). You'll notice that the RowUNIDs are unique, and there
>> are multiple DossierUNIDs in the table. See my sample output with those
>> queries for the results that I'm hoping to achieve.
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
>> in message news:evhI12YUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> OK, what about that ?
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> Select RowUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY RowUNID
>> HTH, Jens SUessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> (SQL Server 2000, SP3a)
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the lowest
>> SortOrder value.
>> However, I have some cases where SortOrder is NULL (which should be
>> treated as the lowest). Additionally, some SortOrder values may be
>> *duplicated*.
>> Consider the following representative data:
>> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
>> [dbo].[#Education]
>> create table [dbo].[#Education]
>> (
>> [RowUNID] uniqueidentifier,
>> [DossierUNID] uniqueidentifier,
>> [SortOrder] int,
>> [TStamp] timestamp
>> )
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'EC70574F-6470-4348-B471-4DE3E81D402C',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '446C0422-2F90-4B01-957C-9ADA2669F385',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '591C60B8-23A8-4D01-A519-3515A1032951',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> I have a couple of queries that yield the results that I'm looking
>> for:
>> select e.*
>> from (
>> select [RowUNIDChecksum] =>> min(binary_checksum(e.[RowUNID]))
>> from (
>> select [DossierUNID] = e.[DossierUNID],
>> [SortOrder] =>> min(isnull(e.[SortOrder], 0))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> e.[DossierUNID] = q.[DossierUNID]
>> and
>> isnull(e.[SortOrder], 0) = q.[SortOrder]
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
>> Or:
>> select e.*
>> from [dbo].[#Education] as e with (nolock)
>> inner join (
>> select [DossierUNID] = e.[DossierUNID],
>> [Key] = min(right(replicate('0', 3) +
>> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) +
>> convert(varchar(36), e.[RowUNID]))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q on right(q.[Key], 36) = e.[RowUNID]
>>
>> But I'm wondering if there's a better (read: more performant ;-) way
>> to accomplish this.
>> Thanks for any help anyone can provide! :-)
>> John Peterson
>>
>>
>>
>>
>|||I thought about something like that -- but I think that we're not able to
correlate the min TStamp and min SortOrder values. (That is, the min TStamp
might be referencing a row that has a larger SortOrder.)
That's the problem I'm having, in a nutshell. Is there any way to use the
GROUP BY clause in some "magic" way, that it will let me retain the original
row from which it came (either by RowUNID or TStamp)?
"Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23ElenXZUFHA.3572@.TK2MSFTNGP12.phx.gbl...
> ok, getting nearer ;-)
>
> What about that one:
> Select E.* from #Education AS E
> INNER JOIN
> (
> Select DossierUNID, min(TStamp) TStamp,
> MIN(SortOrder) SortOrder
> FROM #Education
> GROUP BY DossierUNID
> ) Subquery
> ON Subquery.DossierUNID = E.DossierUNID AND
> Subquery.TStamp = E.TStamp
>
> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
> news:ejM70JZUFHA.2768@.tk2msftngp13.phx.gbl...
>> Yes -- but now I want to somehow *relate* that result set to the
>> appropriate record, in toto, in the original table. If the SortOrder is
>> duplicated, we can pick one at random (though, ideally, I would want the
>> one with the max TStamp value).
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
>> in message news:OZsVxFZUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>> Ok, sorry messed up with your explanation, so you want one one per
>> DossierUNID, you have 4 different DossierUNIDs:
>> --
>> Select count(*), DossierUNID
>> From #Education
>> Group by DossierUNID
>> DossierUNID
>> -- --
>> 2 5548776F-6DEE-4AEA-8CD4-4108D331BE63
>> 4 8B359795-73CE-469C-B797-5AD55A9B023A
>> 6 A552B76B-FCD7-4D7C-BE5E-AA17A730904B
>> 3 CFD039FC-EAF4-4F0B-B6DB-CB143948CE26
>> --
>> You should get the result that query (or did i missed a thing in your
>> description)
>>
>> Select DossierUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY DossierUNID
>> DossierUNID
>> -- --
>> 5548776F-6DEE-4AEA-8CD4-4108D331BE63 -1
>> 8B359795-73CE-469C-B797-5AD55A9B023A 1
>> A552B76B-FCD7-4D7C-BE5E-AA17A730904B 1
>> CFD039FC-EAF4-4F0B-B6DB-CB143948CE26 -1
>>
>> Jens Suessmeyer.
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eJy2s7YUFHA.3176@.TK2MSFTNGP12.phx.gbl...
>> Sorry, Jens -- I probably wasn't clear: I want one row per DossierUNID
>> (not RowUNID). You'll notice that the RowUNIDs are unique, and there
>> are multiple DossierUNIDs in the table. See my sample output with
>> those queries for the results that I'm hoping to achieve.
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
>> wrote in message news:evhI12YUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> OK, what about that ?
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the
>> lowest SortOrder value.
>> Select RowUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY RowUNID
>> HTH, Jens SUessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
>> (SQL Server 2000, SP3a)
>> I'm trying to construct a VIEW that will return *one* row per
>> DossierUNID, and the row should be the DossierUNID row with the
>> lowest SortOrder value.
>> However, I have some cases where SortOrder is NULL (which should be
>> treated as the lowest). Additionally, some SortOrder values may be
>> *duplicated*.
>> Consider the following representative data:
>> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop table
>> [dbo].[#Education]
>> create table [dbo].[#Education]
>> (
>> [RowUNID] uniqueidentifier,
>> [DossierUNID] uniqueidentifier,
>> [SortOrder] int,
>> [TStamp] timestamp
>> )
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'EC70574F-6470-4348-B471-4DE3E81D402C',
>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '446C0422-2F90-4B01-957C-9ADA2669F385',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '591C60B8-23A8-4D01-A519-3515A1032951',
>> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> insert into [dbo].[#Education]([RowUNID], [DossierUNID], [SortOrder])
>> select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>> I have a couple of queries that yield the results that I'm looking
>> for:
>> select e.*
>> from (
>> select [RowUNIDChecksum] =>> min(binary_checksum(e.[RowUNID]))
>> from (
>> select [DossierUNID] = e.[DossierUNID],
>> [SortOrder] =>> min(isnull(e.[SortOrder], 0))
>> from [dbo].[#Education] as e with
>> (nolock)
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> e.[DossierUNID] = q.[DossierUNID]
>> and
>> isnull(e.[SortOrder], 0) = q.[SortOrder]
>> group by e.[DossierUNID]
>> ) as q
>> inner join [dbo].[#Education] as e with (nolock) on
>> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
>> Or:
>> select e.*
>> from [dbo].[#Education] as e with (nolock)
>> inner join (
>> select [DossierUNID] = e.[DossierUNID],
>> [Key] = min(right(replicate('0', 3) +
>> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) +
>> convert(varchar(36), e.[RowUNID]))
>> from [dbo].[#Education] as e with (nolock)
>> group by e.[DossierUNID]
>> ) as q on right(q.[Key], 36) = e.[RowUNID]
>>
>> But I'm wondering if there's a better (read: more performant ;-) way
>> to accomplish this.
>> Thanks for any help anyone can provide! :-)
>> John Peterson
>>
>>
>>
>>
>>
>|||I finally settled on:
select t.*
from [dbo].[#Education] as t with (nolock)
where exists (
select [TStamp] = max(e.[TStamp])
from [dbo].[#Education] as e with (nolock)
where e.[TStamp] = t.[TStamp]
group by e.[DossierUNID],
e.[SortOrder]
having max(e.[TStamp]) = t.[TStamp]
and isnull(e.[SortOrder], 0) <= all
(
select min(isnull(x.[SortOrder], 0))
from [dbo].[#Education] as x with (nolock)
where x.[DossierUNID] = e.[DossierUNID]
group by x.[DossierUNID]
)
)
It looks a little "ugly", but it was more performant that the other
alternatives that I came up with. Further, it avoided parallelization in
the query, which was problematic in this case.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23IiytcaUFHA.1508@.tk2msftngp13.phx.gbl...
>I thought about something like that -- but I think that we're not able to
>correlate the min TStamp and min SortOrder values. (That is, the min
>TStamp might be referencing a row that has a larger SortOrder.)
> That's the problem I'm having, in a nutshell. Is there any way to use the
> GROUP BY clause in some "magic" way, that it will let me retain the
> original row from which it came (either by RowUNID or TStamp)?
>
> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23ElenXZUFHA.3572@.TK2MSFTNGP12.phx.gbl...
>> ok, getting nearer ;-)
>>
>> What about that one:
>> Select E.* from #Education AS E
>> INNER JOIN
>> (
>> Select DossierUNID, min(TStamp) TStamp,
>> MIN(SortOrder) SortOrder
>> FROM #Education
>> GROUP BY DossierUNID
>> ) Subquery
>> ON Subquery.DossierUNID = E.DossierUNID AND
>> Subquery.TStamp = E.TStamp
>>
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:ejM70JZUFHA.2768@.tk2msftngp13.phx.gbl...
>> Yes -- but now I want to somehow *relate* that result set to the
>> appropriate record, in toto, in the original table. If the SortOrder is
>> duplicated, we can pick one at random (though, ideally, I would want the
>> one with the max TStamp value).
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
>> in message news:OZsVxFZUFHA.4056@.TK2MSFTNGP15.phx.gbl...
>> Ok, sorry messed up with your explanation, so you want one one per
>> DossierUNID, you have 4 different DossierUNIDs:
>> --
>> Select count(*), DossierUNID
>> From #Education
>> Group by DossierUNID
>> DossierUNID
>> -- --
>> 2 5548776F-6DEE-4AEA-8CD4-4108D331BE63
>> 4 8B359795-73CE-469C-B797-5AD55A9B023A
>> 6 A552B76B-FCD7-4D7C-BE5E-AA17A730904B
>> 3 CFD039FC-EAF4-4F0B-B6DB-CB143948CE26
>> --
>> You should get the result that query (or did i missed a thing in your
>> description)
>>
>> Select DossierUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY DossierUNID
>> DossierUNID
>> -- --
>> 5548776F-6DEE-4AEA-8CD4-4108D331BE63 -1
>> 8B359795-73CE-469C-B797-5AD55A9B023A 1
>> A552B76B-FCD7-4D7C-BE5E-AA17A730904B 1
>> CFD039FC-EAF4-4F0B-B6DB-CB143948CE26 -1
>>
>> Jens Suessmeyer.
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eJy2s7YUFHA.3176@.TK2MSFTNGP12.phx.gbl...
>> Sorry, Jens -- I probably wasn't clear: I want one row per
>> DossierUNID (not RowUNID). You'll notice that the RowUNIDs are
>> unique, and there are multiple DossierUNIDs in the table. See my
>> sample output with those queries for the results that I'm hoping to
>> achieve.
>>
>> "Jens Süßmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
>> wrote in message news:evhI12YUFHA.612@.TK2MSFTNGP12.phx.gbl...
>> OK, what about that ?
>>> I'm trying to construct a VIEW that will return *one* row per
>>> DossierUNID, and the row should be the DossierUNID row with the
>>> lowest SortOrder value.
>> Select RowUNID, MIN(ISNULL(SortOrder,-1))
>> FROM #Education
>> GROUP BY RowUNID
>> HTH, Jens SUessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>> "John Peterson" <j0hnp@.comcast.net> schrieb im Newsbeitrag
>> news:eHadnvYUFHA.3140@.TK2MSFTNGP14.phx.gbl...
>>> (SQL Server 2000, SP3a)
>>>
>>> I'm trying to construct a VIEW that will return *one* row per
>>> DossierUNID, and the row should be the DossierUNID row with the
>>> lowest SortOrder value.
>>>
>>> However, I have some cases where SortOrder is NULL (which should be
>>> treated as the lowest). Additionally, some SortOrder values may be
>>> *duplicated*.
>>>
>>> Consider the following representative data:
>>>
>>> if (object_id(N'[tempdb].[dbo].[#Education]') is not NULL) drop
>>> table [dbo].[#Education]
>>> create table [dbo].[#Education]
>>> (
>>> [RowUNID] uniqueidentifier,
>>> [DossierUNID] uniqueidentifier,
>>> [SortOrder] int,
>>> [TStamp] timestamp
>>> )
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '3C2CE763-DAF8-46CD-8B09-67979CA68325',
>>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'C98A6838-2523-4507-A4F3-FFC6FAD51329',
>>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 1
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '4E0733DF-B15B-4895-925B-6A4ADF0C1BC1',
>>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 2
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '885B1138-E13A-4D87-8F5A-B8C379B1F328',
>>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 3
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'D14DDA37-1E51-42BF-BC0A-25744AB638C6',
>>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 4
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'EC70574F-6470-4348-B471-4DE3E81D402C',
>>> 'A552B76B-FCD7-4D7C-BE5E-AA17A730904B', 5
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'B3010834-9CBD-4075-84BA-D0AA3B2D8420',
>>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'D0C43744-B59A-48BC-99ED-61A9D759261C',
>>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', NULL
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'BC97CB2C-C629-47EB-8F84-3EF97B99C664',
>>> 'CFD039FC-EAF4-4F0B-B6DB-CB143948CE26', 1
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select 'D5E90FED-3023-470B-ADA2-4F809B746F81',
>>> '8B359795-73CE-469C-B797-5AD55A9B023A', 1
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '446C0422-2F90-4B01-957C-9ADA2669F385',
>>> '8B359795-73CE-469C-B797-5AD55A9B023A', 2
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '8383D1B5-ACBD-46CD-B4A0-A83AB23D6CCC',
>>> '8B359795-73CE-469C-B797-5AD55A9B023A', 3
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '591C60B8-23A8-4D01-A519-3515A1032951',
>>> '8B359795-73CE-469C-B797-5AD55A9B023A', 4
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '0EEED645-27B0-459C-B964-BF1B5BCB35C0',
>>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>>> insert into [dbo].[#Education]([RowUNID], [DossierUNID],
>>> [SortOrder]) select '1544DF52-F93A-4871-86C9-E67DB976BDB7',
>>> '5548776F-6DEE-4AEA-8CD4-4108D331BE63', NULL
>>>
>>> I have a couple of queries that yield the results that I'm looking
>>> for:
>>>
>>> select e.*
>>> from (
>>> select [RowUNIDChecksum] =>>> min(binary_checksum(e.[RowUNID]))
>>> from (
>>> select [DossierUNID] = e.[DossierUNID],
>>> [SortOrder] =>>> min(isnull(e.[SortOrder], 0))
>>> from [dbo].[#Education] as e with
>>> (nolock)
>>> group by e.[DossierUNID]
>>> ) as q
>>> inner join [dbo].[#Education] as e with (nolock) on
>>> e.[DossierUNID] = q.[DossierUNID]
>>> and
>>> isnull(e.[SortOrder], 0) = q.[SortOrder]
>>> group by e.[DossierUNID]
>>> ) as q
>>> inner join [dbo].[#Education] as e with (nolock) on
>>> binary_checksum(e.[RowUNID]) = q.[RowUNIDChecksum]
>>>
>>> Or:
>>>
>>> select e.*
>>> from [dbo].[#Education] as e with (nolock)
>>> inner join (
>>> select [DossierUNID] = e.[DossierUNID],
>>> [Key] = min(right(replicate('0', 3) +
>>> convert(varchar(3), isnull(e.[SortOrder], 0)), 3) +
>>> convert(varchar(36), e.[RowUNID]))
>>> from [dbo].[#Education] as e with (nolock)
>>> group by e.[DossierUNID]
>>> ) as q on right(q.[Key], 36) = e.[RowUNID]
>>>
>>>
>>> But I'm wondering if there's a better (read: more performant ;-)
>>> way to accomplish this.
>>>
>>> Thanks for any help anyone can provide! :-)
>>>
>>> John Peterson
>>>
>>>
>>
>>
>>
>>
>>
>

Friday, March 30, 2012

How can I retrieve a recordset from a matrix-like table?

How can I resolve this database error, in VS 2005?

How can I request row #3 in a dataset?

How can I remove a duplicate row?

How can I remove a duplicate row?

How can I remove a duplicate row?

Wednesday, March 28, 2012

How can I refactor these GROUP BY queries to be faster?

How can I refactor these GROUP BY queries to be faster?

How can I refactor these GROUP BY queries to be faster?

Back devices

Blog Archive

About Me