3 Replies - 682 Views - Last Post: 24 May 2016 - 08:51 AM

#1 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

SQL - Show only the highest Value

Posted 24 May 2016 - 07:42 AM

Hello Guys,

So my SQL Listing is almost ready, one thing isnīt working.
I need only the highest value in my query.

Here is my Listing:
SELECT MieterNr, Count(MieterNr) AS "Anzahl an Buchungen", Sum(Mietpreis) AS Mieteinnahmen
FROM Belegung
WHERE MieterNr = MieterNr
GROUP BY MieterNr;



So what i need is only the "MieterNr"(in english lodger Number) with the highest Mietpreis(in english rent).
I Tried to use Max(Mietpreis), GROUP BY Mietpreis, WHERE MieterNr = MaxMietpreis.
What can i do?

Regards
Tobias

Is This A Good Question/Topic? 0
  • +

Replies To: SQL - Show only the highest Value

#2 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: SQL - Show only the highest Value

Posted 24 May 2016 - 08:07 AM

You could ORDER BY SUM(Mietrpries) DESC LIMIT 1
Was This Post Helpful? 0
  • +
  • -

#3 Bonekit  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 09-May 16

Re: SQL - Show only the highest Value

Posted 24 May 2016 - 08:20 AM

Limit is not my solution, great idea but i need an other solution.
Was This Post Helpful? 0
  • +
  • -

#4 ybadragon  Icon User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: SQL - Show only the highest Value

Posted 24 May 2016 - 08:51 AM

oh I'm sorry I forgot this was access. There is no LIMIT in access. Instead use the order by clause, then change your select to SELECT TOP 1...

This post has been edited by ybadragon: 24 May 2016 - 08:51 AM

Was This Post Helpful? 1
  • +
  • -

Page 1 of 1