RE: ORDER BY IIF(SoldUnits > 400, 0, 1), Description

Works great, Vishal ! Thanks !

"Vishal Parkar" wrote:

> Check if example shown in following query works for you.
>
> create table #t(dsc varchar(50), units int)
>
> insert into #t
> select 'After dark' ,390 union all
> select 'All about eve',810 union all
> select 'Beginers' ,500 union all
> select 'Black Jack',300 union all
> select 'Cast a spell',220 union all
> select 'Charlie' ,410
>
> --Query.
>
> select * from #t
> order by case when units > 400 then 1 else 0 end desc, dsc asc
>
>
>
> "Junior" wrote:
>
> > Sure, Vishal... Thanks for the interest. Here it goes.
> >
> > Records ordered by description :
> >
> > 'After dark' (390 units sold)
> > 'All about eve' (810 units sold)
> > 'Beginers' (500 units sold)
> > 'Black Jack' (300 units sold)
> > 'Cast a spell' (220 units sold)
> > 'Charlie' (410 units sold)
> >
> > The way I want them to be (notice that the ones which sold more than 400
> > units are grouped alphabetically on top, and the rest above them, also
> > alphabetically ordered) :
> >
> > 'All about eve' (810 units sold)
> > 'Beginers' (500 units sold)
> > 'Charlie' (410 units sold)
> > 'After dark' (390 units sold)
> > 'Black Jack' (300 units sold)
> > 'Cast a spell' (220 units sold)
> >
> > Thanks again !
> >
> >
> >
> > "Vishal Parkar" wrote:
> >
> > > can you post sample data and expected result set. The statement "but want to
> > > put the 3 top sold items on top, all all others after them." is confusing. if
> > > you post sample data and expected result set then it wd be easy to understand.
> > >
> > > "Junior" wrote:
> > >
> > > > Hi everyone...
> > > >
> > > > I have to order a list of items in SQL 2000, but want to put the 3 top sold
> > > > items on top, all all others after them. I'd find out the break number easily
> > > > (400 in the example). It'd be two groups of description ordered items, as in :
> > > >
> > > > 'All about eve' (810 units sold)
> > > > 'Beginers' (500 units sold)
> > > > 'Charlie' (410 units sold)
> > > > 'After dark' (390 units sold)
> > > > 'Black Jack' (300 units sold)
> > > > 'Cast a spell' (220 units sold)
> > > > ...and so on.
> > > >
> > > > As IIF doesn't work in SQL Server, I tried to use 'ORDER BY (CASE SoldUnits
> > > > when SoldUnits >= 400 THEN 0 ELSE 1 END), Descricao', but an error occurs
> > > > (Incorrect syntax near '>'.). What is the solution for that ?
> > > >
> > > > Thanks very much !
> > > >
> > > > Junior
> > > >
.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Graphical Union-Query Builder?
    ... If you are limiting the input in each of the sub-queries in your Union ... Use the filtered queries as the input to your Union query: ... Most "functional IT users" will not know anything about SQL. ... I didn't know that fields of subsequent queries in a Union could have ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: How to get a distinct count of result set of multople table joins?
    ... since the UNION syntax removes duplicate rows automatically. ...   "SORT " in your execution plan. ... The base query is an outer join. ...
    (comp.databases.oracle.misc)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)