Avatar of shaunwingin
shaunwinginFlag 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
Microsoft ExcelMySQL Server

Avatar of undefined
Last Comment
Rob

8/22/2022 - Mon
Rob

You use the LIMIT keyword in your query

SELECT * FROM tbl LIMIT 0,9;
ASKER
shaunwingin

Will this select the oldest or newest records? Need newest.
Rob

Then add the ORDER BY

SELECT * FROM tbl ORDER BY some_column LIMIT 0,9;
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rob

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?
Rob

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
ASKER
shaunwingin

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rob

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question