Return Duplicate Rows

I want to select all my columns that have the same number twice....but i dont know how....I tried doit it this way

Select Distinct sti.parent_topic_id, st.description_short, sti.sort_order, count(st.description_short)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, st.description_short, sti.sort_order

--Having count(sti.sort_order) > 1

Order By parent_topic_id

I even tried sti.sort_order = sti.sort_order and that does not work eithier

But it returns everything...when i uncomment the having clause it does not return anything....I want my end result to be

2 TheName 10 1

2 SomthingElse 10 1

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

select *
from sam_topic_items sti

inner join sam_topic st

on sti.topic_id = st.id
inner join
(
select sti.topic_id
from sam_topic_items sti
inner join sam_topic st
on sti.topic_id = st.id
where st.type = 'Topic'
group by sti.topic_id
having count(*) > 1
) d
on st.topic_id = d.topic_id|||

Thanks 4 the response but this returns all my rows with the sam ID's instead of the same sort order...I have a table like this

id sort_order

1 2

1 2

1 1

1 3

I only want to return.......

id sort_order

1 2

1 2

I thought if i did it like you had it but tweak it a little it would work....but it still returns all the rows

select *

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

inner join

(

select sti.parent_topic_id, sti.sort_order

from sam_topic_items sti

inner join sam_topic st

on sti.parent_topic_id = st.id

where st.type = 'Topic'

group by sti.parent_topic_id, sti.sort_order

having count(*) > 1

) d

on sti.sort_order = d.sort_order

|||

I think you need to join by both sort_order and topic_id.

A more basic question is if you know where the duplication is coming from.

Are there multiple records in the topic table (of type 'topic') which have the same ID?|||There are multiple items in the topic_item tables that have the same number in thier sort order...and I only want to select those id's that have the same number in there sort_order....my topic table has a PK of id and my topic_items table has a PK of topic ID and a PK of parent_topic_id.....|||

When I say primary key I mean a column (or columns) which uniquely identify a single record in the table. That's means there cannot be duplicates in the primary key. I am interested in topic_id vs. parent_topic_id are these the same field or related fields. They look like foreign keys (fields which link to a primary key on another table).

The main issue in your query seems to be multiple records in the topic_items table with the same topic_ID (or parent_topic_ID I'm not sure) and the same sort order.

If this is the case then:

SELECT topic_ID, sort_order
FROM topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1

should give a list of these records (but only 1 instance of each combination). Then joining it back to the other tables should give the record list you want.

SELECT ST.ID, STI.description_short, STI.sort_order
FROM (
SELECT topic_ID, sort_order
FROM sam_topic_items
GROUP BY topic_ID, sort_order
HAVING count(*) > 1
) TS
INNER JOIN sam_topic_items STI
ON (TS.topic_ID = STI.topic_ID) AND
(TS.sort_order = STI.sort_order)
INNER JOIN sam_topic ST
ON (STI.topic_id = ST.id)
WHERE (ST.type = 'Topic')
ORDER BY ST.ID

This is all testing by topic_id. If it should be parent_topic_id then change it to this throughout the query.

There is also still the question of what the type field does in selecting records (as I say do you have records other than type='Topic' in the sam_topic table?). Depending upon exactly what this does it may cause problems with the SQL to pick up the duplicates.

|||

Yes there is other records other than "Topic" there is also "ROOT" but I am just worrying about "Topic" and nothing else....This is how I had to change the query it works now partially

SELECT STI.parent_topic_id, ST.description_short, STI.sort_order

FROM (

SELECT parent_topic_id, sort_order

FROM sam_topic_items

GROUP BY parent_topic_id, sort_order

HAVING count(sort_order) > 1

) TS

INNER JOIN sam_topic_items STI

ON (TS.sort_order = STI.sort_order)

INNER JOIN sam_topic ST

ON (STI.topic_id = ST.id)

WHERE (ST.type = 'Topic')

ORDER BY STI.parent_topic_id

but it still does not return only the rows with duplicate sort order for a particular parent_topic_id this query returns

id description_short sort_order

2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Customer Revenue: 1
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Meeting Type: 2
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2130 Region: 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 10mm 3
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1B 4
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1
2131 < 1mm 1.....thanks 4 the help though!!!

See how it returns all these results for the same thing?...in my table there is only one "<1mm" for the parent_topic_id of 2131 but this returns like 20 of them...I dont know how it did that?

|||

Change your having clause to:

HAVING count(*) >1

|||Yea i did that I still get the same result though?|||

Yeah I get it. I didn't see the whole thing.

You see they have the same id and the same sort_order but diffrent description names.......when i run the above query it returns everything for some reason...I am not clear on how i need to set the criteria to only pull those rows with the same sort_order number for a particular ID? any help?

You need to take the description name out of the group by, otherwise you will count the values with the same id but different descriptions as different rows. To count the number of occurences of a different description for the same id you need to take the description out of your group.

|||Ok here is a better question this query

Select Distinct sti.parent_topic_id, st.Type, sti.sort_order, count(sti.sort_order)

From sam_topic_items sti

Inner Join sam_topic st On sti.topic_id = st.id

Where st.type = 'Topic'

Group By sti.parent_topic_id, sti.sort_order, st.Type

Having count(sti.sort_order) > 1

Order By parent_topic_id

Returns this data exactly

parent_topic_id type sort_order the last column is the number of occurences that the sort_order has.....like if you look at the first row of data.....there are 3 occurences of 0 for parent_topic_id 2334...

2334 TOPIC 0 3
2398 TOPIC 32 2
2399 TOPIC 16 2
2430 TOPIC 22 2
2447 TOPIC 26 2
2447 TOPIC 12 2
2447 TOPIC 15 2
2452 TOPIC 1 3
2505 TOPIC 37 2
2505 TOPIC 52 2
2505 TOPIC 3 2
2507 TOPIC 6 2
2526 TOPIC 32 2
2549 TOPIC 54 2
2549 TOPIC 52 2
2562 TOPIC 0 2
2722 TOPIC 2 2
2880 TOPIC 0 2
2995 TOPIC 0 2
2997 TOPIC 0 2
3000 TOPIC 0 2
3001 TOPIC 0 2
3040 TOPIC 6 2
3152 TOPIC 26 2
3244 TOPIC 0 3
3524 TOPIC 0 2
3605 TOPIC 0 2
3638 TOPIC 0 2
3721 TOPIC 2 2
3723 TOPIC 1 2
4082 TOPIC 4 2
4100 TOPIC 1 3
4181 TOPIC 11 2
4220 TOPIC 0 2
4261 TOPIC 2 2
4262 TOPIC 1 2
4302 TOPIC 3 2
4302 TOPIC 1 2
4302 TOPIC 2 2
4439 TOPIC 0 2
5029 TOPIC 2 3
5032 TOPIC 2 4
5042 TOPIC 2 6
5042 TOPIC 11 2
5071 TOPIC 2 3
5297 TOPIC 2 2
5297 TOPIC 3 2
5297 TOPIC 1 2
5368 TOPIC 52 2
5368 TOPIC 3 2
5368 TOPIC 36 2
5368 TOPIC 48 2
5368 TOPIC 37 2
5368 TOPIC 54 2
5849 TOPIC 45 2
5882 TOPIC 16 2
5882 TOPIC 42 2
5882 TOPIC 54 2
5882 TOPIC 11 2
5882 TOPIC 55 3
5882 TOPIC 60 2
5882 TOPIC 40 2
6032 TOPIC 0 3
6137 TOPIC 0 2
8897 TOPIC 3 2
9461 TOPIC 2 2
9461 TOPIC 1 2
9598 TOPIC 11 2
9608 TOPIC 54 2
10345 TOPIC 0 2
10366 TOPIC 2 2
10627 TOPIC 17 2
11992 TOPIC 42 2
12081 TOPIC 4 2
12083 TOPIC 3 3
12298 TOPIC 20 2
12701 TOPIC 0 2
12716 TOPIC 9 4
12717 TOPIC 4 2
12750 TOPIC 6 2
12755 TOPIC 3 2
12760 TOPIC 1 2
12760 TOPIC 3 2
12760 TOPIC 4 2
12760 TOPIC 2 2
12926 TOPIC 5 3
12990 TOPIC 8 6
12998 TOPIC 9 4
12998 TOPIC 6 2
13001 TOPIC 2 2
13069 TOPIC 5 2
13069 TOPIC 1 2
13132 TOPIC 8 2
13134 TOPIC 4 2
13349 TOPIC 5 3
13429 TOPIC 4 2
13429 TOPIC 19 3
13429 TOPIC 1 2
13429 TOPIC 3 2
13429 TOPIC 6 2

Now using the above query how would i write a sub query to use the data return in this result to show some other data in the same result set?

|||

Like what? I thought thats what you wanted to show?

Your result set is going to be grouped.

It will only have a single row for each group.

You can do simple tricks like putting an aggregate function like MIN function to show things like the description but you will only show a single description for the grouped row.

What is it exactly that you are trying to show?

Have you considered an sp rather than a single query?

ALSO, please not that you DO NOT need st.Type in the grouping or the field list since your where criteria clearly states that it has to be equal to 'TOPIC'.

|||

thanks liz.....

If you look at the results for the query....and look at the id of 2334 it has 3 instances of 0 in that id....I want to be able to show

2334 TheNAme 0

2334 The OtherName 0

2334 The OtherName 0

I want to show the id the description and the sort order.....Only for those duplicate sort orders and thats it.....I could write a vb or C# app to do it....but i need to learn sql, haha as you can see i dont know it well!!! thanks!!

|||

It is real ugly but, if you are using 2 values you need to convert them to a char string and use them in the where clause together in the outer query...

Select sti.parent_topic_id, st.description_short, sti.sort_order
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order) IN
(
Select convert(char(20), sti.parent_topic_id) + convert(char(20), sti.sort_order)
From sam_topic_items sti
Inner Join sam_topic st On sti.topic_id = st.id
Where st.type = 'Topic'
Group By sti.parent_topic_id, sti.sort_order, st.Type
Having count(sti.sort_order) > 1
Order By parent_topic_id
)

I don't know if this will execute properly because I don't have the right tables to test it but it is the general idea...

|||

First off please explain to me what the convert method did? I need to know what that did to the query to make it act like so....

BUT you are a WIZ!!!!!!!!! unless I am just that mentally slow THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!THANK!!! YOU!!!!......

return duplicate records

Hello experts,

I'm trying the run the following query with specific intentions.

I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.

I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?

Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);

alexAlex,

There are a few solutions. Two are (might have typos, but you should be
able to get the idea):

select Employee_last_name, Employee_first_name
from tbl_employee
join (
select '009' as id union all
select '008' as id union all
select '007' as id union all
select '007' as id union all
select '006' as id
) as IDs
on IDs.id = tbl_employee.employee_id

or to make the specification of ids simpler:

declare @.ids varchar(1000)
set @.ids = '009008007007006'
declare @.idlength int
set @.idlength = 3

select Employee_last_name, Employee_first_name
from tbl_employee
join a_permanent_table_of_integers_from_0_to_whatever as Nums
on employee_id = substring(@.ids,@.idlength*n+1,@.idlength)
and n < len(@.ids)/@.idlength
-- [n] is the column name for the permanent table and should
-- be that tables primary key

-- Steve Kass
-- Drew University
-- http://www.stevekass.com
alex wrote:

Quote:

Originally Posted by

Hello experts,
>
I'm trying the run the following query with specific intentions.
>
I would like the query to return 5 results; i.e., 4 distinct and one
duplicate. I am only getting, however, 4 distinct records. I would
like the results from the '007' id to spit out twice.
>
I'm not using 'distinct,' and I've tried 'all.' I realize that I
could put my 5 employee id's in a table and do a left or right join; I
would like to avoid that, however. Any thoughts?
>
Select
Employee_last_name,
Employee_first_name

Quote:

Originally Posted by

>>From tbl_employee

Where employee_id in (
'009',
'008',
'007',
'007',
'006'
);
>
alex
>

|||>I would like the query to return 5 results; i.e., 4 distinct and one duplicate. <<

The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:

SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'|||--CELKO-- (jcelko212@.earthlink.net) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>I would like the query to return 5 results; i.e., 4 distinct and one

duplicate. <<

Quote:

Originally Posted by

>
The easy way is a UNION, based on a guess about the DDL you did bother
to post and the uniquness of emp_id:
>
SELECT last_name, first_name
FROM Personnel
WHERE emp_id IN ('009', '008', '007', '006')
UNION
SELECT last_name, first_name
FROM Personnel
WHERE emp_id = '007'

Joe, I thought you knew SQL? This query will not return the results
that Alex was asking for.

Why is left as an exercise to the reader.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
*****************************************/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
*****************************************/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unexpected behaviors, this is why it
will be removed in some future release. Did you consider using the modern outer join syntax?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.google.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> *****************************************/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> *****************************************/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give an
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so much
Does this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
David Portas
SQL Server MVP
|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:

> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the data
> or to add a key in the correct order and then use that to drive your query.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give a
n
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so muchDoes this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
David Portas
SQL Server MVP
--|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:

> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the dat
a
> or to add a key in the correct order and then use that to drive your query
.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>

Retrieve Last Record SQL Stmt

Is there a way to get the last record from the duplicate records. To give an
example, I have table that has records as follows:
BranchPO
branch PO POLine Amount POsuf
555 147586 10 399.00 2
555 147586 10 .00 1
555 147586 10 27.00 0
I can't use POSuf in my criteria. branch, PO and POline are the only fields
that I can set my query. These being duplicate records, how do I get the
last record which is
branch PO POLine Amount POsuf
555 147586 10 399.00 2
If I use Progress GL language, I can use the following stmt to get the last
record.
Find Last BranchPO where branch = 555 and po = 147586 and poline = 10
no-error.
My result will be the last created record
branch PO POLine Amount
555 147586 10 399.00
Please help.
Thank you so muchDoes this table have a key? In SQL tables are unordered and there is no
reliable way to retrieve rows in the order in which they were inserted
unless you persist that information as data in the table. If the last
inserted row is important to you then your best bet is to recreate the data
or to add a key in the correct order and then use that to drive your query.
If the insertion order is not so important and you just need to remove
duplicates then you can GROUP BY branch, po, poline.
--
David Portas
SQL Server MVP
--|||Thank you so very much for your suggestion.
Insertion order is not important b'cos I'm dumping the data into SQL server
from the ERP system.
I will try by Removing the duplicates with GROUP BY branch, po, poline. I
feel positive that this works.
Thank you
"David Portas" wrote:
> Does this table have a key? In SQL tables are unordered and there is no
> reliable way to retrieve rows in the order in which they were inserted
> unless you persist that information as data in the table. If the last
> inserted row is important to you then your best bet is to recreate the data
> or to add a key in the correct order and then use that to drive your query.
> If the insertion order is not so important and you just need to remove
> duplicates then you can GROUP BY branch, po, poline.
> --
> David Portas
> SQL Server MVP
> --
>
>