SQL Server Select Record based on datetime and user type -

- 

     id|   type   |  datetime    |     ---| ---------|--------------|      1 | admin    | 2016-01-03   |      2 | user     | 2016-01-07   |      3 | user     | 2016-01-08   |      4 | admin    | 2016-01-04   |      5 | user     | 2016-01-01   |      6 | user     | 2016-01-03   |      7 | user     | 2016-01-05   |      8 | user     | 2016-01-09   |

lets have table above , need rearrange based on datetime. first record must admin latest datetime among type of admin only. second fifth record must type of user sort datetime descending. again, sixth record must type of admin second latest datetime. expected result query shown in table below.

expected result:

     id|   type   |  datetime    |     ---| ---------|--------------|      4 | admin    | 2016-01-04   |      8 | user     | 2016-01-09   |      3 | user     | 2016-01-08   |              2 | user     | 2016-01-07   |      1 | admin    | 2016-01-03   |      7 | user     | 2016-01-05   |      6 | user     | 2016-01-03   |      5 | user     | 2016-01-01   |

can guys give me idea on how write query expected result?

i found way without issues of ntile when count not divisible amount of users want group. based on gofr1's script, suggest use:

select  id,         [type],         [datetime] (     select  id,             [type],             [datetime],             row_number() on (order [datetime]) - 1 idx     yourtable     [type] = 'admin'     union     select  id,             [type],             [datetime],             floor((row_number() on (order [datetime]) - 1) / 3) idx     yourtable     [type] = 'user'     ) p order idx, [type]

Comments

Post a Comment

Popular posts from this blog

Lists in Python -

-
i noticed can multiply list scalar behavior weird if multiply: [2,3,4] * 3 i get: [2,3,4,2,3,4,2,3,4 ] understand results it's for? there other weird operations that? the main purpose of operand initialisation. example, if want initialize list 20 equal numbers can using loop: arr=[] in range(20): arr.append(3) an alternative way using operator: arr = [3] * 20 more weird , normal list operation on lists can find here http://www.discoversdk.com/knowledge-base/using-lists-in-python
Read more

android - can not access to progress bar in an other activity -

-
i have objects in layout video view,text view , on comes this: <?xml version="1.0" encoding="utf-8"?> <android.support.v7.widget.cardview android:id="@+id/cardview_mypost" android:layout_width="match_parent" android:layout_height="wrap_content" android:layout_marginbottom="@dimen/activity_vertical_margin" app:cardcornerradius="@dimen/activity_vertical_margin" app:cardelevation="@dimen/activity_vertical_margin" xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto"> <relativelayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="fill_parent" android:layout_height="wrap_content" android:background="@color/web_withe_color" android:animatelayoutchanges="@anim/anim_list" android:padding="8dp" > <!-- thu...
Read more

java - Vaadin 7 Pass Data Between Views -

-
i have 2 vaadin views , want make search process these 2 views. first view contains textfield search parameter , button seach process. want is, once user click search button, want send search parameter second view , call search method dao classes here display search result. vaadin newbie , dont have idea how appreciated. the classic approach pass data view via parameters of view (this allows bookmark search). see view interface: void enter(viewchangelistener.viewchangeevent event) this view navigated to. method called before view shown on screen. event.getparameters() may contain parameters relevant view. you can send parameters originating view via navigator. e.g. navigator.navigateto("search/searchterm") // `search` view name, `searchterm` passed param see example in handling uri fragment path of book of vaadin
Read more