mysql distinct statement

Dear experts,
I have table named "followers"
I have information about users on that table. User's id, lattitude and longtitude and the date when the information is recorded on my table.
but it repetitives, same userid has more than one row. I need to select the last inserted userid with latitude and longtitude information.

I want to select the last recoreded userid with the lat, lng columns

My columns are:

id ( auto_increment )
userid
lat
lng
date (datetime)

thank you
LVL 1
BRMarketingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swatantra BhargavaTechnical Specialist/DeveloperCommented:
Try with below
With MySQLI
echo "New record has id: " . mysqli_insert_id($con);


with MySQL Query
SELECT LAST_INSERT_ID();
Return the first AUTO_INCREMENT value that was set by the most recent INSERT or UPDATE statement.
0
Adrian ThompsonSoftware Engineer and Consultant for Website Development and Online MarketingCommented:
Hello BR,

If you want to select the last recorded userid with the lat, lng columns you can use this MySQL query:

SELECT id,lat,lng FROM followers WHERE id=LAST_INSERT_ID();
0
BRMarketingAuthor Commented:
Dear Adrian and Swatantra
it doesn't working

But it works like this

SELECT userid,lat,lng,date FROM `followers` group by userid order by date DESC
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Adrian ThompsonSoftware Engineer and Consultant for Website Development and Online MarketingCommented:
Hello BR,

Sorry I just noticed you have a column for `id` and a column for `userid` so in that case, the statement should be:

SELECT userid,lat,lng FROM `followers` WHERE id=LAST_INSERT_ID();

That should work fine. If not, please let me know the output and what is wrong. Thanks
0
BRMarketingAuthor Commented:
Dear Adrian Thompson,,
it doesn't work eigther
0
Adrian ThompsonSoftware Engineer and Consultant for Website Development and Online MarketingCommented:
When you execute the command, what is the output?
0
BRMarketingAuthor Commented:
it brings only the column names not the data
İt's empty
0
Adrian ThompsonSoftware Engineer and Consultant for Website Development and Online MarketingCommented:
Also please let me know the output of this query:

SELECT LAST_INSERT_ID();

If the result is 0 then you haven't yet done any updates to your database, which means the query I gave you above will not work. In this case, try doing an update then run the query again.
0
BRMarketingAuthor Commented:
LAST_INSERT_ID()
0

it is 0
0
BRMarketingAuthor Commented:
when I use it like this "SELECT LAST_INSERT_ID(id) from followers"
it brings all information
0
BRMarketingAuthor Commented:
it is correct to use it like this?

SELECT userid,lat,lng,date FROM `followers` group by userid order by date DESC
it works.. I think it does the same thing
0
Adrian ThompsonSoftware Engineer and Consultant for Website Development and Online MarketingCommented:
LAST_INSERT_ID() gives the most recently generated ID from your MySQL connection but you are relying on data created earlier so in this case it is returning zero.

This will work:

SELECT userid,lat,lng,date FROM `followers`ORDER BY id DESC LIMIT 1
0
Adrian ThompsonSoftware Engineer and Consultant for Website Development and Online MarketingCommented:
Your statement will always work if there is only ONE entry in the database for that userid and date:

SELECT userid,lat,lng,date FROM `followers` group by userid order by date DESC

In this case, if there are two entries on the same date by different users, you can get an incorrect result. This is because you are ordering by date, which may not be unique.

With the following statement, you are ordering by the unique and auto-incremented "id" so you are guaranteed the correct result:

SELECT userid,lat,lng,date FROM `followers`ORDER BY id DESC LIMIT 1
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BRMarketingAuthor Commented:
Thank you Adrian
0
Swatantra BhargavaTechnical Specialist/DeveloperCommented:
Can you paste your query execution code? What are you using mysql or mysqli?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.