OT: need SQL help, asking the gurus ... |
|
Porsche, and the Porsche crest are registered trademarks of Dr. Ing. h.c. F. Porsche AG.
This site is not affiliated with Porsche in any way. Its only purpose is to provide an online forum for car enthusiasts. All other trademarks are property of their respective owners. |
|
OT: need SQL help, asking the gurus ... |
SirAndy |
Jul 22 2004, 12:30 PM
Post
#1
|
Resident German Group: Admin Posts: 41,640 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
ok, here's the problem:
let's say i have a table with 2 rows like the example below. in the second row, i have a integer status value (iStatus). i need a query that allows me to find the value that occurs the most in the table, in the example below, that would be "3". i'm not interested in how many 3s there are, all i need is to find out which iStatus value shows up the most in the table ... (IMG:style_emoticons/default/pray.gif) Attached image(s) |
lapuwali |
Jul 22 2004, 12:36 PM
Post
#2
|
Not another one! Group: Benefactors Posts: 4,526 Joined: 1-March 04 From: San Mateo, CA Member No.: 1,743 |
Oracle, MySQL, SQLServer, DB2, etc? It will make a difference. There is no single "SQL".
Just off the top of by non-guru head, I'd say you're looking at using MAX(COUNT(*)), probably with a sub-select, but I can't give you exact syntax w/o knowing which SQL, and I only know MySQL and Oracle (and I don't know Oracle well). |
SirAndy |
Jul 22 2004, 12:40 PM
Post
#3
|
Resident German Group: Admin Posts: 41,640 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
QUOTE(lapuwali @ Jul 22 2004, 11:36 AM) Oracle, MySQL, SQLServer, DB2, etc? It will make a difference. There is no single "SQL". Just off the top of by non-guru head, I'd say you're looking at using MAX(COUNT(*)), probably with a sub-select, but I can't give you exact syntax w/o knowing which SQL, and I only know MySQL and Oracle (and I don't know Oracle well). count(*) won't work. as i said, i don't need to know how MANY 3s there are ... just ANSI SQL, the query should be easy enough to run anywhere, i'm hoping i don't need some crazy syntax for what i want. Andy |
eeyore |
Jul 22 2004, 01:18 PM
Post
#4
|
Senior Member Group: Members Posts: 889 Joined: 8-January 04 From: meridian, id Member No.: 1,533 Region Association: None |
select top 1 count(iStatus) as StatCount, sDescription from {tablename} group by sDescription
order by StatCount You'll need to replace "{tablename}" with the actual name |
SirAndy |
Jul 22 2004, 01:28 PM
Post
#5
|
Resident German Group: Admin Posts: 41,640 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
QUOTE(Cloudbuster @ Jul 22 2004, 12:18 PM) select top 1 count(iStatus) as StatCount, sDescription from {tablename} group by sDescription order by StatCount nope, that just returns the first row in the table ... Attached image(s) |
Deluxe247 |
Jul 22 2004, 01:28 PM
Post
#6
|
Newbie Group: Members Posts: 6 Joined: 14-March 04 From: Centennial, CO Member No.: 1,792 |
To get the mode do this:
SELECT TOP 1 COUNT(*) AS 'Number of repeats', rowName AS Number FROM dbo.tblName GROUP BY rowName ORDER BY COUNT(*) DESC It will return how many times the number occured and which number. You can then pull whatever you want from the result; the actual number. |
SirAndy |
Jul 22 2004, 01:38 PM
Post
#7
|
Resident German Group: Admin Posts: 41,640 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
QUOTE(Deluxe247 @ Jul 22 2004, 12:28 PM) To get the mode do this: SELECT TOP 1 COUNT(*) AS 'Number of repeats', rowName AS Number FROM dbo.tblName GROUP BY rowName ORDER BY COUNT(*) DESC It will return how many times the number occured and which number. You can then pull whatever you want from the result; the actual number. perfect! that worked ... (IMG:style_emoticons/default/pray.gif) thank you very much, you just saved me a ton of work! next (IMG:style_emoticons/default/beer3.gif) will be on me, Andy |
vortrex |
Jul 22 2004, 01:41 PM
Post
#8
|
Senior Member Group: Members Posts: 1,687 Joined: 24-December 02 From: SF, CA Member No.: 4 Region Association: None |
make a text file.
cat filename | cut -f2 -d" " | sort -rn | uniq | head -1 |
SirAndy |
Jul 22 2004, 02:02 PM
Post
#9
|
Resident German Group: Admin Posts: 41,640 Joined: 21-January 03 From: Oakland, Kalifornia Member No.: 179 Region Association: Northern California |
QUOTE(vortrex @ Jul 22 2004, 12:41 PM) make a text file. cat filename | cut -f2 -d" " | sort -rn | uniq | head -1 it'll be part of a humongous script that'll migrate data from one DB into another. no time and patience to manually fiddle with text files. i'm hoping to run this overnight and the new and oh so much better database will be ready to go in the morning ... (IMG:style_emoticons/default/biggrin.gif) Andy |
Lo-Fi Version | Time is now: 17th May 2024 - 07:49 AM |
All rights reserved 914World.com © since 2002 |
914World.com is the fastest growing online 914 community! We have it all, classifieds, events, forums, vendors, parts, autocross, racing, technical articles, events calendar, newsletter, restoration, gallery, archives, history and more for your Porsche 914 ... |