java - How to select multiple columns but group by only one column in Hypersql database(HSQLDB)? -


i using hsqldb-2.3.3 version, here i'm showing simple mysql query.

example:

select name,age,emailid,country players group country;

but if same query in hypersql shows error, "expression not in aggregate or group columns public.players.name"

and if apply group every column selected select statement, display results aggregation of column.

my question is, how display multiple columns 1 column aggregate (group by) in hypersql ??

table players:

pid name emailid country region age

1 samual samual@gmail.com india delhi 25

2 vino vino@gmail.com india delhi 20

3 john john@gmail.com india delhi 20

4 andy andy@gmail.com india delhi 22

5 brian brian@hotmail.com america delhi 21

6 dew dew@hotmail.com america delhi 24

7 kris kris@hotmail.com america delhi 25

8 william william@hotmail.com india delhi 26

9 george george@hotmail.com india delhi 23

10 peter peter@gmail.com india delhi 19

11 tom tom@gmail.com america delhi 20

12 andre andre@hotmail.com india delhi 20

expected result:

name age emailid country

brian 21 brian@hotmail.com america

samual 25 samual@gmail.com india

the implementation of gorup in mysql different other databases follow sql standard. in case, query shouldn't work.

the result expect contains person lowest pid each country. can write query explicitly asks this:

select name, age, emailid, country players      pid in (select min(pid) players group country)

the query first finds lowest pid each country. selects 2 rows contain these pid values.


Comments

Post a Comment

Popular posts from this blog

sublimetext3 - what keyboard shortcut is to comment/uncomment for this script tag in sublime -

Image
i have script tag in code, keyboard shortcut comment/uncomment script tag in sublime. after selecting script open , close tag. <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script> i pressing ctrl + shift + / , getting this /*<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script>*/ also pressed ctrl + / , got this // <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> // </script> instead of <!-- <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"> </script> --> script open , close tag in 2 lines not in 1 line , not have spaces too. for mac,high light lines want comment. hold 'command , / ' commented. for windows: high line or select lines ,then hold ctrl + / if not work: ...
Read more

ios - MKMapView fails to load tiles with HTTP 410 error -

i have problem mkmapview . map fails load tiles when zoom in. -(void)mapviewdidfailloadingmap:(mkmapview *)mapview witherror:(nserror *)error error: domain=geoerrordomain code=-204 "(null)" userinfo={simpletilerequesterunderlyingerrors=( "error domain=geoerrordomain code=-204 \"(null)\" userinfo={httpstatus=410, nserrorfailingurlstringkey= http://gspe19.ls.apple.com/tile.vf?flags=1&style=20&size=2&scale=0&v=11037825&z=15&x=6205&y=12336&sid=0246704635757302674107153038443966765357&accesskey=1454685602_q3bvuyvhbdxsso0a_j0fk7eyq9b21npshv7grlzr4wfkkhxb4vo7%2blxcgsxj4zzhvhtalvwsypa3plu60cdrmrfwmwcybgrla9mchv%2fhorhotu9agi72vqp9ukzw%2b0gkqfrhpcw4xr%2f%2fttvgjz7wu4u4kna8k2rvvq%2foffhjq7oo4nyectvy0ur4i9d3sxf%2btn9dcxu8agdrjignb }", ... edit: seems related cache somehow, i'm not sure. problem disappears time after loading same map region in maps application. thanks in advance i analysed , d...
Read more

c# - How to utilize EF and LINQ to add filters and specify tables, columns, filters and order by dynamically -

Image
i have database structure (all parent , childs tables), what doing now want create ad hoc reporting page, let user select tables , columns in these tables. when user select tables , columns, can add filters (is in list or contains or filter etc..) columns. i send information json web service create, in web service planning use entityframework required dataset. what have done i able create html ui, web services, database layer repositories , uow, database etc.. question i mean can this, var result = context.parenta.include("child1.subchild1").include(....).where(.. but not sure how can specify columns want or add filters. for specifying column names can use select linq query : var result = context.parenta.include("child1.subchild1").include(....).where(..).select(s=> new {name = s.parentname , subname = s.subchild.name }); for adding filters need define them in clause .where(p=>p.name.contains("somevalue")) ...
Read more