Easiest way to make this work? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Easiest way to make this work?

Author

Topic

pithed2
Starting Member

3 Posts

Posted - 2006-07-18 : 22:38:01

I'm working with a table that's keyed by Member# and a sequencing number within that member#.

A user wants a report that is sorted by the sequence #1's last name and first name, but then is followed by that seq #1's following seq #'s under the Member#.

For example:

Last Name = Smith
First Name = Alex
Mbr# = 10
Seq# = 01
Smith, Alex, 10, 01
Then under Alex Smith would be his wife and children (who may not have the same last name as him).
Smith, Jenny, 10, 02
Jones, Jimmy, 10, 03
Smith, Julie, 10, 04

Next alphabetic Seq# 1
Smith, Brian, 25, 01
Smith, Lucy, 25, 02
Williams, Gina, 25, 03
Williams, Gordon, 25, 04

Next alphabetic Seq# 1
Smith, Bryana, 18, 01
Johnson, John, 18, 02
Smith, Jorge, 18, 03

I can do this fairly easily with multiple queries, but due to the size of the database and that it's a web query using ODBC, I need to try to make into one SQL statement if possible.

I would assume I could create a sub query that first pulls all the Seq#1's ordered by Last and First, but can I then tie that back to the same table to by a join to get the seq#02 and up??

Thanks for any help ahead of time.

Andy

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-18 : 22:42:00

Can you post some more data so we can get a clearer view of what you're playing with?

Cheers,

Tim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 23:08:30

this ?

select *
from   table
order by Mbr#, Seq#

Maybe you should post the table structure, some sample data and the result that you want.

KH

pithed2
Starting Member

3 Posts

Posted - 2006-07-18 : 23:27:49

Sorry. Here's a quick, short form. The table is not normalized and resides on an AS400. So I'm only going to give a few fields from it.

Table: Member_Master
Fields: Member# (11 int), Seq#(2 int), First_Name(15 string), Last_Name (25 string)

Keyed by Member#, Seq# uniquely

The data would look like this:
10, 01, Alex, Smith
10, 02, Jenny, Smith
10, 03, Jimmy, Jones
10, 04, Julie, Smith
18, 01, Bryana, Smith
18, 02, John, Johnson
18, 03, Jorge, Smith
25, 01, Brian, Smith
25, 02, Lucy, Smith
25, 03, Gina, Williams
25, 04, Gordon, Williams

I'm probably not on the same syntax as most of you as I work on an AS400 primarily. Anyway, I need to sort all these members by last name, then first, however, I need to have the seq# > 1, return under the seq# 01 in seq# order, rather than alphabetical.

So I need to receive this, from that data:

Smith, Alex, 10, 01
Smith, Jenny, 10, 02
Jones, Jimmy, 10, 03
Smith, Julie, 10, 04
Smith, Brian, 25, 01
Smith, Lucy, 25, 02
Williams, Gina, 25, 03
Williams, Gordon, 25, 04
Smith, Bryana, 18, 01
Johnson, John, 18, 02
Smith, Jorge, 18, 03

The order being:

Select *
From Member_Master
Where Seq# = 01
Order by Last_Name, First Name

But then I need to take each of those and join them through an inner join?? back to Member_Master to get the seq# > 1 ordered by seq#.

Is that possible??

On the 400, I would just read through the Member Master file sorted by last name, first name for seq# = 01. Once I found a member, I would then loop back through the file, sorted by member# and seq# and get all the seq#'s in order. However, I have to do this for a php script and can't quite do that. The table is very large (5 mil records), so I have to find a way to make it timely on the internet. Once again, I apologize for jumping between lingo (table/records), but I'm much more used to the AS400 lingo.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-18 : 23:53:25

"The table is not normalized and resides on an AS400."
This is a MS SQL SERVER forum site so we works primary on MS SQL. This works in MS SQL Server not sure about AS400.

declare @member table
(
	member_no	int,
	seq_no		int,
	first_name	varchar(10),
	last_name	varchar(10)
)

insert into @member
select	10, 01, 'Alex',   'Smith'	union all
select	10, 02, 'Jenny',  'Smith'	union all
select	10, 03, 'Jimmy',  'Jones'	union all
select	10, 04, 'Julie',  'Smith'	union all
select	18, 01, 'Bryana', 'Smith'	union all
select	18, 02, 'John',   'Johnson'	union all
select	18, 03, 'Jorge',  'Smith'	union all
select	25, 01, 'Brian',  'Smith'	union all
select	25, 02, 'Lucy',   'Smith'	union all
select	25, 03, 'Gina',   'Williams'	union all
select	25, 04, 'Gordon', 'Williams'


select	c.last_name, c.first_name, c.member_no, c.seq_no
from	@member p inner join @member c
on	p.member_no	= c.member_no
where	p.seq_no	= 1
order by p.last_name, p.first_name, c.seq_no

/* RESULT
last_name  first_name member_no   seq_no      
---------- ---------- ----------- ----------- 
Smith      Alex       10          1
Smith      Jenny      10          2
Jones      Jimmy      10          3
Smith      Julie      10          4
Smith      Brian      25          1
Smith      Lucy       25          2
Williams   Gina       25          3
Williams   Gordon     25          4
Smith      Bryana     18          1
Johnson    John       18          2
Smith      Jorge      18          3

(11 row(s) affected)
*/

KH

pithed2
Starting Member

3 Posts

Posted - 2006-07-19 : 10:07:34

That worked perfectly. Thank you very much for your help!!

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources