Three Query Questions for SQL

[Jokeheads]

I've got a couple questions.

I'm trying to update a VB program of mine that accesses MS Access as a Database.

The database is sorted by one column, and I want it to be able to read the top 10 "rows" after that sort, but I have no idea how to do it. The only SQL I have ever written, if I could call it that, was copied and edited to my needs. I can understand and edit simple coding, but I have no education as to write it myself.

I'm looking to do 3 things:
Find the top 10 sorted by Column A, and grab info from Columns A,B,C,D.... Once finding a way to access the top 10 "rows" (Sorry, I forget the name for them) I could write code to do the rest.
2) After being sorted by Column A, I'm looking to find the "ranking" of that "row" by its spot in the sort. (If it is row 56 after the sort, it's ranking would be, say 56/150)
- So after being sorted by Column A, I want to find the "Ranking" of Column B by column A. For example, sorting 10 people by age, and then querying the name of the 2nd oldest person of the 10 people. It would return the ranking "2".
3) Lastly, I'm looking to total up all values in Column A. Is there a sum command, like excel that could do this?

I realize I am being vague, but perhaps after getting a lead, I can be more specific.

 

[r937]

1)

SELECT TOP 10 ColumnA, ColumnB, ColumnC
FROM table
ORDER BY ColumnA

2)

SELECT columns
, ( SELECT COUNT(*) + 1
FROM table
WHERE ColumnA < T.ColumnA ) AS Rank
FROM table as T

3)

SELECT SUM(ColumnA)
FROM table


r937.com | rudy.ca

 

[Jokeheads]

That was VERY helpful, however, when I say I am an SQL noob, I really am. It seems like 1) would return an SQL table, how would I go about accessing individual information from it? What would 2) return?. Would 3 just return an int that I could easily store in a VB variable.

I guess what I'm really curious about is how do I get the information from the SQL code directly into VB variables. Preferably, I'd have an array for each column (or one matrix) for 1), an array for 2) and just an integer for 3). I'm sorry, but while this code makes sense to me, and I can make it work partly, I still am not sure how to make it work fully for me.

Thanks for you help thus far,
Jason

 

[AlexCuse]

Have a look at VB's recordset, connection, and command objects...

This is really more an ADO/ADO.net question, if you need help with that try one of these forums (depending on version you're using)

forum796
forum222

Good Luck,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[Jokeheads]

At least let me know what values they return, and how I can access them. I can most likely figure it out from there. For now, it would be stupid to do a String operator on an Integer, don't you think? (Just an analogy)

 

[r937]

For now, it would be stupid to do a String operator on an Integer, don't you think?

inconthievable!!


(yes, the VB forum would seem to be more appropriate)

r937.com | rudy.ca

 

[Jokeheads]

Once I know what each returns, I will be able to write code to do what I want, but for now: what do each of three give me? A table? An array? An int? Etc...I need to know what the postcondition is before I can proceed with the next step.

 

[r937]

every SELECT query returns a query result set which just happens to conform to the same structure as a table

1) will return 10 rows
2) will return as many rows as in the source table
3) will return one row

try running those queries directly in Access to see how they work

r937.com | rudy.ca

 

[Jokeheads]

That makes a lot more sense, but how would the second one help me if it returns all the rows?

 

[r937]

it returns all rows with a ranking

if you want to query a particular row only, just add a WHERE clause



r937.com | rudy.ca