Shaun Wingrin
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
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
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;
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 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
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT * FROM tbl LIMIT 0,9;