Hi Monks,

I have a database table named location The table will look like

ID (Primary Key, Auto Increment)	State	City
1	Virginia	Norfolk
2	Virginia	Chesapeake
3	Virginia	Virginia Beach
4	Indiana	Evansville
5	Indiana	Fort Wayne
6	Virginia	Falls Church
7	Indiana	Indianapolis
8	Washington	Seatle
9	Washington	Spokane

I need to sort this table by primary key, but grouped by state. ie, the result will be

ID (Primary Key, Auto Increment)	State	City
1	Virginia	Norfolk
2	Virginia	Chesapeake
3	Virginia	Virginia Beach
6	Virginia	Falls Church
4	Indiana	Evansville
5	Indiana	Fort Wayne
7	Indiana	Indianapolis
8	Washington	Seatle
9	Washington	Spokane

Few things need to remember is

1. We cant use SQL's "GROUP BY" as group by will return only one entry for "virginia", One for "Indiana" etc...
2. We cant give SQL's "ORDER BY" as it wont group.
3. We can not sort with State as it will take "Indiana" above "Virginia"
4. I am NOT allowed to alter the structure of table.
5. I am NOT allowed to use temperory tables or files

Can anybody give some idea? I wish to have a "perl" solution.

Thanks in advance :)

--VC

Updates


• Edited the table to fix typo mistakes.
• Added two entries in table(8 and 9) for clarificaton

---

In reply to Sorting and Grouping with two different fields by atemon

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":





---

1. Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
2. Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
3. Read Where should I post X? if you're not absolutely sure you're posting in the right place.
4. Please read these before you post! —
   • How do I compose an effective node title?
   • How do I post a question effectively?
   • Markup in the Monastery
5. Posts may use any of the Perl Monks Approved HTML tags:

   a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

6. You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

   	For:		Use:
   	&		&
   	<		&lt;
   	>		&gt;
   	[		&#91;
   	]		&#93;

7. Link using PerlMonks shortcuts! What shortcuts can I use for linking?
8. See Writeup Formatting Tips and other pages linked from there for more info.