2. PHPRunner General questions
  3. Use of distinct problems
This topic is locked

Use of distinct problems

 		11/21/2016 3:41:25 AM
PHPRunner General questions
spettinato author

Hi all, i'm trying to use distinct in sql server query with phprunner, and i see that there is a big problem, and a bug.
This query, working normally:

SELECT distinct

dbo.checkins.[user],

dbo.userPushtoken.platform,

dbo.checkins.place

FROM dbo.checkins

INNER JOIN dbo.userPushtoken ON dbo.checkins.[user] = dbo.userPushtoken.[user]

ORDER BY dbo.userPushtoken.platform
Gives a result query in phprunner in this way:

SELECT top 20 distinct [dbo].[checkins].[user], [dbo].[userPushtoken].[platform], [dbo].[checkins].[place] FROM [dbo].[checkins] INNER JOIN [dbo].[userPushtoken] ON [dbo].[checkins].[user] = dbo.userPushtoken.[user] ORDER BY 2 ASC
So the problem is that phprunner adds "top 20" in the wrong place.

BUG: if i add a condition at the end of query so it is "WHERE (dbo.checkins.place =364)" it works. Cause Phprunner doesn't add TOP 20.
How can i resolve to use a distinct in query?
Thanks all for replying
P.S.anyway if someone find me a way to use distinct properly, i think phprunner becomes a good software
HJB 11/21/2016

Let me try to explain WHY at least me never saw the need to dive deeper into any SQL DB programming.

Time is definitely over since years to run DB syntax trouble-shooting as generated web code can do fine.

So..., I'm just dropping the code below for inspiration purposes only while at least me must say:
I didn't like to read yours "i think phprunner becomes a good software" as no else product than PHPR

can cover highest percentage of needs, say, the wording used, here "becomes a good" is direct to the

goodness of that very one who is sitting in front of the screen (DEFINITELY NO NEED TO BLAME AND FLAME).
quote excerpt ex http://www.asprunner.com/forums/topic/18159-sql-query-based-on-count-on-report-page/

Select

table_id,

field1,

field2,

field3,

matchcode

FROM tablename

WHERE (matchcode IN

(SELECT matchcode FROM tablename AS tablename_1

GROUP BY matchcode

HAVING (COUNT (*) > 1)))

unquote
Sergey Kornilov admin 11/21/2016

As you can see on this page the use of DISTINCT is not supported. However there is an easy workaround. You can create a view in SQL Server on the top of your SQL Query and use this view as a datasource in PHPRunner.
spettinato author 11/27/2016



As you can see on this page the use of DISTINCT is not supported. However there is an easy workaround. You can create a view in SQL Server on the top of your SQL Query and use this view as a datasource in PHPRunner.


Very interesting, is there some example on this?
Sergey Kornilov admin 11/27/2016

Sure, check CREATE VIEW article in MySQL's manual:

http://dev.mysql.com/doc/refman/5.7/en/create-view.html