Link to home
Start Free TrialLog in
Avatar of Shaun Wingrin
Shaun WingrinFlag for South Africa

asked on

EXCEL MYSQL ODBC Select and display records

Say,
 
I have an ODBC link in place.
How can I select top 10 records from a table and display in an excel Sheet?

Can I use a call statement to an existing query in Mysql which selects the top 10 records, else another method is fine?
tx
Avatar of Rob
Rob
Flag of Australia image

You use the LIMIT keyword in your query

SELECT * FROM tbl LIMIT 0,9;
Avatar of Shaun Wingrin

ASKER

Will this select the oldest or newest records? Need newest.
Then add the ORDER BY

SELECT * FROM tbl ORDER BY some_column LIMIT 0,9;
If you were to sort the table manually, just by looking at it, what column would you sort on and would you sort it ascending or descending?
Regarding your other question about calling a query and displaying in excel.  There are two methods that come to mind but would depend on how your users would use the spreadsheet and how the data is displayed.

Method 1: Re-use an existing query (good for large data sets and pivot tables)
This is a bit long winded, but good for when the layout of the query you're returning doesn't change.  The idea is you create a dummy query to your database and then as you want to show new data you change the sql for that query and tell it to refresh.

Method 2: Manually parse each record (good for straight tables)
This has its downfalls, especially when you may be pivoting data but gives you flexibility on where the data is displayed.  eg The query returns a row of data at a time so essentially you could put that data anywhere in the spreadsheet you want.  You are not tied to a table / pivot layout that you would in Method 1.

Basically I would more or less use Method 1 but there are occasions when Method 2 is ok, usually with small datasets ie few records
Method 2 would work.
assume a simple table with fields
date
time
shiftno

how can I select top 10 records and display in excel sheet result cel A1 downwards?
Would use call query to select the data.
ASKER CERTIFIED SOLUTION
Avatar of Rob
Rob
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial